Excel2000: Opening *.csv through VBA and International Settings

A

Arvi Laanemets

Hi

A Firm leasing various IT devices has a web page, where customers can see
various info about their leased devices. There is a possibility to save this
info as a CSV-file.

When I open such CSV-file directly from Excel, then it's all OK - all data
is in right columns (there is 22 of them).
When I open the same CSV-file from VBA (I need to generate rental payments
table from it), then all data are messed up hopeless. The code which opens
the CSV-file is:
....
' Asking for source file
varFileTitle = "Open source file!"
varFileFilter = "Templates (*.CSV), *.CSV"
varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
FileFilter:=varFileFilter)
If varSourceFile = "False" Then
MsgBox ("Data import was stopped because no source file was
selected!")
Exit Sub
End If

' Opening source workbook
Workbooks.Open Filename:=varSourceFile
....

The reason is, that the CSV-file is using Estonian settings - semicolon is
used as data delimiter. But when CSV-file is opened from VBA, comma is used
as data delimiter instead. (In Estonian settings, comma is used as decimal
delimiter, and additionally in CSV-table in some columns with text data
[and in some column headers] the comma is simply used for punctuation).

How to force Excel VBA to use semicolons as delimiters when opening a
CSV-file. When there doesn't exist a way to do this, I have to open the
CSV-file as an ASCII file, and to use some heavy programming to read all
data into Excel - I hope I can avoid this.
 
N

Niek Otten

Hi Arvi,

Couldn't you rename the file a .txt file? If you then manually open it you get a wizard in which you can specify date formats ans
separators. You might be able to record that in a macro and use that as a start for your own code.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| A Firm leasing various IT devices has a web page, where customers can see
| various info about their leased devices. There is a possibility to save this
| info as a CSV-file.
|
| When I open such CSV-file directly from Excel, then it's all OK - all data
| is in right columns (there is 22 of them).
| When I open the same CSV-file from VBA (I need to generate rental payments
| table from it), then all data are messed up hopeless. The code which opens
| the CSV-file is:
| ...
| ' Asking for source file
| varFileTitle = "Open source file!"
| varFileFilter = "Templates (*.CSV), *.CSV"
| varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
| FileFilter:=varFileFilter)
| If varSourceFile = "False" Then
| MsgBox ("Data import was stopped because no source file was
| selected!")
| Exit Sub
| End If
|
| ' Opening source workbook
| Workbooks.Open Filename:=varSourceFile
| ...
|
| The reason is, that the CSV-file is using Estonian settings - semicolon is
| used as data delimiter. But when CSV-file is opened from VBA, comma is used
| as data delimiter instead. (In Estonian settings, comma is used as decimal
| delimiter, and additionally in CSV-table in some columns with text data
| [and in some column headers] the comma is simply used for punctuation).
|
| How to force Excel VBA to use semicolons as delimiters when opening a
| CSV-file. When there doesn't exist a way to do this, I have to open the
| CSV-file as an ASCII file, and to use some heavy programming to read all
| data into Excel - I hope I can avoid this.
|
|
| --
| Arvi Laanemets
| ( My real mail address: arvi.laanemets<at>tarkon.ee )
|
|
 
A

Arvi Laanemets

Hi

Couldn't you rename the file a .txt file? If you then manually open it you
get a wizard in which you can specify date formats ans
separators. You might be able to record that in a macro and use that as a
start for your own code.
Click to expand...



I'm afraid I can't :-(((
I'm trying to avoid any manual editing when possible - it'll be extremly
difficult to explain to ladies in financial department how they must do it,
and to be sure they never don't do anything in wrong way :))
As end result, leasing payments data (DeviceNo, Date, Sum) must be imported
into Access database. I can't read CSV-file directly into Access, because it
isn't fully in table format. And anyway I have to generate wholly new table
based on CSV table, because CSV table contains a single row for every
device - along with payment sums for payment period, payment period lengths,
payment period start and end dates, etc. And the CSV-file name is generated
auttomatically and is always unique - no way there to link it into Access
without manual work again.

With an Excel file as intermediate step I hoped to minimize possible
missteps. The needed steps would be:
1. The user opens web page, clicks on "Export to Excel", and saves the
CSV-file.
2. Then he/she opens special Excel file, clicks on button "Import Data", and
points on saved CSV-file - the new lease payments table is generated. (This
table is always linked to Access database). The Excel file is closed.
3. When the Access form with lease info is activated, and there exist any
rows (determined by DeviceNo and PaymentDate) in linked Excel file, which
are missing in according Access table, or have different Sum, a button
"Import lease payments for <FirmName>" is displayed. When the button is
clicked, all missing data are imported, and all changed data are edited. The
button is hidden again after that.


Thanks for a try anyway.
(With an additional saving step, there is no need for text file - a better
way is to save the manually opened CSV-file in Excel format)
 
S

Stefi

Hi Arvi,

I suppose you use XL2003, because I had similar tasks and when working with
XL2000 (Hungarian settings are the same as Estonian) it recognized correctly
the semicolon separated text files, but after having upgraded to XL2003 I
experienced the same as you.

I found a workaround:
Opened the .CSV file:
Workbooks.Open Filename:=feladfuzet
'count No of rows
feladdarab = Columns("A:A").Find("*", [A1], , , xlByRows,
xlPrevious).Row
Call CSVtoXL(feladdarab)

I recorded a macro using Text to Columns and made a sub from it:
Sub CSVtoXL(sorokszama)
Dim Terjedelem As Range
Set Terjedelem = Range("A1:A" & sorokszama)
Terjedelem.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1)), TrailingMinusNumbers:=True
End Sub


Not very nice but works! What can we do if Microsoft decides to change
working solutions to non-working ones? I hope it helps to ou!

Regards,
Stefi

„Arvi Laanemets†ezt írta:
Hi

A Firm leasing various IT devices has a web page, where customers can see
various info about their leased devices. There is a possibility to save this
info as a CSV-file.

When I open such CSV-file directly from Excel, then it's all OK - all data
is in right columns (there is 22 of them).
When I open the same CSV-file from VBA (I need to generate rental payments
table from it), then all data are messed up hopeless. The code which opens
the CSV-file is:
....
' Asking for source file
varFileTitle = "Open source file!"
varFileFilter = "Templates (*.CSV), *.CSV"
varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
FileFilter:=varFileFilter)
If varSourceFile = "False" Then
MsgBox ("Data import was stopped because no source file was
selected!")
Exit Sub
End If

' Opening source workbook
Workbooks.Open Filename:=varSourceFile
....

The reason is, that the CSV-file is using Estonian settings - semicolon is
used as data delimiter. But when CSV-file is opened from VBA, comma is used
as data delimiter instead. (In Estonian settings, comma is used as decimal
delimiter, and additionally in CSV-table in some columns with text data
[and in some column headers] the comma is simply used for punctuation).

How to force Excel VBA to use semicolons as delimiters when opening a
CSV-file. When there doesn't exist a way to do this, I have to open the
CSV-file as an ASCII file, and to use some heavy programming to read all
data into Excel - I hope I can avoid this.
Click to expand...
 
A

Arvi Laanemets

Hi



I suppose you use XL2003, because I had similar tasks and when working
with
Click to expand...


No, it's Excel2000 (but we have Outlook2003 installed in all our computers,
because Exchange Server doesn't co-operate well with Outlook2000).
Btw., Access2000 in same computer recognizes semicolons as delimiters, but I
as I explained in another posting, in Access there will be other problems.

XL2000 (Hungarian settings are the same as Estonian) it recognized
correctly
the semicolon separated text files, but after having upgraded to XL2003 I
experienced the same as you.

I found a workaround:
Opened the .CSV file:
Workbooks.Open Filename:=feladfuzet
'count No of rows
feladdarab = Columns("A:A").Find("*", [A1], , , xlByRows,
xlPrevious).Row
Call CSVtoXL(feladdarab)

I recorded a macro using Text to Columns and made a sub from it:
Sub CSVtoXL(sorokszama)
Dim Terjedelem As Range
Set Terjedelem = Range("A1:A" & sorokszama)
Terjedelem.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1),
Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1),
Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1), _
Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57,
1), Array(58, 1), Array( _
59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1),
Array(64, 1), Array(65, 1), _
Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70,
1), Array(71, 1), Array( _
72, 1), Array(73, 1), Array(74, 1)), TrailingMinusNumbers:=True
End Sub


Not very nice but works! What can we do if Microsoft decides to change
working solutions to non-working ones? I hope it helps to ou!
Click to expand...



With first opening, the data is split into columns (different number of them
row-wise), because there are p.e. numbers like 1234,50. Your workaround
works only, when all data remain in column A. Of-course I can concantenate
them before spliting again, but there will be some spaces lost in text
entries for sure. And this all will be even more cumbersome.


Thanks anyway
 
N

Niek Otten

Can't you import the file as one column (maybe by renaming it .txt first) and then use the Data>Text to columns feature, where you
can specify a semicolon as separator?
This could all be hidden in a macro so the users doesn't see it.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi
|
|
| > Couldn't you rename the file a .txt file? If you then manually open it you
| > get a wizard in which you can specify date formats ans
| > separators. You might be able to record that in a macro and use that as a
| > start for your own code.
|
|
|
| I'm afraid I can't :-(((
| I'm trying to avoid any manual editing when possible - it'll be extremly
| difficult to explain to ladies in financial department how they must do it,
| and to be sure they never don't do anything in wrong way :))
| As end result, leasing payments data (DeviceNo, Date, Sum) must be imported
| into Access database. I can't read CSV-file directly into Access, because it
| isn't fully in table format. And anyway I have to generate wholly new table
| based on CSV table, because CSV table contains a single row for every
| device - along with payment sums for payment period, payment period lengths,
| payment period start and end dates, etc. And the CSV-file name is generated
| auttomatically and is always unique - no way there to link it into Access
| without manual work again.
|
| With an Excel file as intermediate step I hoped to minimize possible
| missteps. The needed steps would be:
| 1. The user opens web page, clicks on "Export to Excel", and saves the
| CSV-file.
| 2. Then he/she opens special Excel file, clicks on button "Import Data", and
| points on saved CSV-file - the new lease payments table is generated. (This
| table is always linked to Access database). The Excel file is closed.
| 3. When the Access form with lease info is activated, and there exist any
| rows (determined by DeviceNo and PaymentDate) in linked Excel file, which
| are missing in according Access table, or have different Sum, a button
| "Import lease payments for <FirmName>" is displayed. When the button is
| clicked, all missing data are imported, and all changed data are edited. The
| button is hidden again after that.
|
|
| Thanks for a try anyway.
| (With an additional saving step, there is no need for text file - a better
| way is to save the manually opened CSV-file in Excel format)
|
|
|
| --
| Arvi Laanemets
| ( My real mail address: arvi.laanemets<at>tarkon.ee )
|
|
 
S

Stefi

Yes, I had a luck because my CSV file was imported from a DOS system in which
decimal delimiter was represented by "." (period) therefore rows were not
broken, all data remained in column A!

Regards,
Stefi
 
A

Arvi Laanemets

OK. I decided for saving CSV-file manually to *.xls. So currently steps to
get payment table into Access are:
1. User saves the data from web page as CSV-file.
2. User opens CSV-file with Excel, and saves as Excel workbook - all columns
are split properly.
3. User opens special Excel file, and starts a procedure, which generates a
new lease payments table. The Excel file is saved and closed automatically.
4. In Access project, when any changed data appear in linked Excel table,
user can start a procedure for refreshing lease payments table.

An additional step compared to original plan (no. 2), but an easy one. And
as bonus, user can replace CSV-file name, which is a random sequence of
numbers, with a more meaningfull one.


Arvi Laanemets
 
A

Arvi Laanemets

Hi


NickHK said:
What about making a web query from Excel directly ?
Click to expand...

At first, this web query thing is a thin ice form me. I tried it once
following Excel Help, but the wizard wanted some table name I didn't have
any clue at. And Help was as useless as always :)) . As there wasn't any
urgent need for it, then I dropped it.

And as second, the original table has little use for me, and I can't
generate a new one from it using query language only. In Excel, I have some
earlier-made procedures which I modified slightly, and which now will do all
this work in a go. Maybe in future I'll write some equivalent for Access
too, but at moment I didn't have any time for it.
 
N

NickHK

What is the web page address ?

NickHK

Arvi Laanemets said:
Hi




At first, this web query thing is a thin ice form me. I tried it once
following Excel Help, but the wizard wanted some table name I didn't have
any clue at. And Help was as useless as always :)) . As there wasn't any
urgent need for it, then I dropped it.

And as second, the original table has little use for me, and I can't
generate a new one from it using query language only. In Excel, I have some
earlier-made procedures which I modified slightly, and which now will do all
this work in a go. Maybe in future I'll write some equivalent for Access
too, but at moment I didn't have any time for it.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA macro to auto import new CSV file from fixed directory 1
VBA code To Merge All .csv files into a single excel sheet (data start from row 11th) 2
CSV-file as semicolon se 2
Forcing Excel to Import .CSV data 2
How write into text file at first and last line with excel vba? 3
Extract data from csv file 8
Opening .CSV file 3
Reading data from CSV file 4

Top