Import and export mixed data from and to csv file in VBA6

[jliu66]

Dear my friends,

I am working a project to import and export a large amount data. Right now I save the data in spreadsheet but time consuming. I want to import and export those mixed data directly from and to csv file saved in C:\. The data file is comma delimited as usual. Assume both files are saved in C:\

In the imported csv file, each column is a record including chars and numerical. but I do not know how many columns will have in the imported CSV file. The number of rows will be the same for each column. I want to read them into a array in VBA for numericals. In the following example, there are 8 columns but unkown so I want to check how many columns in CSV file. The file looks like as following:
7,AEP,AES,ATG,ATO,AYE,CEG,CMS
7,25537101,00130H105,1204106,49560105,17361106,210371100,125896100
7,USD,USD,USD,USD,USD,USD,USD
7,USA,USA,USA,USA,USA,USA,USA
7,55101010,55105010,55102010,55102010,55101010,55105010,55103010
7,393684000,653175000,77652000,80832000,162853000,178585000,220095000
1/5/1993,32.875,4.4094,18.625,15.5,23.75,23.125,18.375
1/6/1993,33,4.4094,18.75,15.5833,23.75,23.125,18.5
1/7/1993,32.625,4.3285,18.75,15.3333,23.75,22.75,18.375
1/8/1993,32.125,4.4903,18.4375,15.25,23.5625,22.875,18.375
1/11/1993,32.75,4.5307,18.3125,15.4167,23.6875,23,18.5
1/12/1993,32.75,4.4296,18.375,15.6667,23.625,23.125,18.375
1/13/1993,33.125,4.4903,18.5,15.6667,23.75,23.375,18.5
1/14/1993,33.125,4.4903,18.5625,15.6667,23.875,23.5,18.625
1/15/1993,33,4.5712,18.6875,15.5833,24.125,23.5,18.625
1/18/1993,33.125,4.6117,18.75,15.5,24.0625,23.75,18.75

Also I want to export a mixed data array with chars and numerical to a CSV file. The first row is a header. Each record is in one row. The file format looks like as following:
Equity #,No,Date,Long,Entry,Name1,Currency,Country
1,1,35860,AEP,48.38,AMERICAN ELECTRIC POWER,USD,USA
2,2,38412,AEP,33.71,AMERICAN ELECTRIC POWER,USD,USA
4,1,35094,CMS,31,CMS ENERGY CORP,USD,USA
4,2,35135,CMS,29.13,CMS ENERGY CORP,USD,USA
4,3,35325,CMS,29.5,CMS ENERGY CORP,USD,USA
5,4,36055,CMS,41.81,CMS ENERGY CORP,USD,USA
6,5,38686,CMS,13.98,CMS ENERGY CORP,USD,USA
8,1,37377,AEP,45.68,AMERICAN ELECTRIC POWER,USD,USA
8,2,37550,AEP,20.15,AMERICAN ELECTRIC POWER,USD,USA
10,1,34348,AEP,35.63,AMERICAN ELECTRIC POWER,USD,USA
11,2,35067,DPL,17.08,DPL INC,USD,USA
11,3,35268,DPL,15.42,DPL INC,USD,USA
14,1,35061,DUK,23.75,DUKE ENERGY CORP,USD,USA
14,2,35395,DUK,23.31,DUKE ENERGY CORP,USD,USA
15,3,37552,AEP,21.78,AMERICAN ELECTRIC POWER,USD,USA
16,1,34429,AEP,30.88,AMERICAN ELECTRIC POWER,USD,USA
17,2,37074,AEP,46.92,AMERICAN ELECTRIC POWER,USD,USA
18,3,38751,AEP,36.1,AMERICAN ELECTRIC POWER,USD,USA
20,1,35906,AEP,48.13,AMERICAN ELECTRIC POWER,USD,USA

Can you show me a sample VBA code to do both import from and export to CSV files with comma delimiter? Thanks so much in advance.

Cheers,

John

[hopalongcassidy]

It looks like you confused columns and rows in your description in the first paragraph. If this is the case, I can do what you ask, but it will require some programming.

I will charge a fee of $150.00 USD. If you're interested, respond to this thread.

Hoppy

[yello]

Where do you import the data from? Is it from a database? What database?

Also you are mentioning that the volume is huge. Give an approximate number of records, you are trying to import or export.

[jliu66]

Where do you import the data from? Is it from a database? What database?

Also you are mentioning that the volume is huge. Give an approximate number of records, you are trying to import or export.

Well, the data is from csv file in the directory for import. for Export, it will save to a CSV file when the calculation is done. The size of records is 20,000 by 20,000. It takes a lot of computer time to write and read from spreadsheets. I want to speed up the process and read in & out from CSV files.

Thanks so much.

John

[jliu66]

Where do you import the data from? Is it from a database? What database?

Also you are mentioning that the volume is huge. Give an approximate number of records, you are trying to import or export.

Well, the data is from csv file in the directory for import. for Export, it will save to a CSV file when the calculation is done. The size of records is 20,000 by 20,000. It takes a lot of computer time to write and read from spreadsheets. I want to speed up the process and read in & out from CSV files.

Thanks so much.

John

[yello]

CSV file is a text file. So you don't need to use a spreadsheet for opening the file.

You can open the CSV file with the following code

Dim ObjFso
Dim ObjInputFile
Dim StrOneLine
Dim ArrColumns
Dim IntColumnCount

Set ObjFso=CreateObject("Scripting.FileSystemObject")
Set ObjInputFile = ObjFso.OpenTextFile(<file name>)

'Then you can read from the file line by line as follows
StrOneLine = ObjInputFile.ReadLine

'To get the different columns into an array you can split it by comma
ArrColumns = Split(StrOneLine, ",")

'To know the number columns you can use the UBound function
IntColumnCount = UBound(ArrColumns) + 1

'To know the type of data you can use any of the following functions
If IsNumeric(ArrColumns(0)) Then
'Its a numeric data
ElseIf IsDate(ArrColumns(0)) Then
'Its a date
etc..

Hope this helps

[jliu66]

CSV file is a text file. So you don't need to use a spreadsheet for opening the file.

You can open the CSV file with the following code

Dim ObjFso
Dim ObjInputFile
Dim StrOneLine
Dim ArrColumns
Dim IntColumnCount

Set ObjFso=CreateObject("Scripting.FileSystemObject")
Set ObjInputFile = ObjFso.OpenTextFile(<file name>)

'Then you can read from the file line by line as follows
StrOneLine = ObjInputFile.ReadLine

'To get the different columns into an array you can split it by comma
ArrColumns = Split(StrOneLine, ",")

'To know the number columns you can use the UBound function
IntColumnCount = UBound(ArrColumns) + 1

'To know the type of data you can use any of the following functions
If IsNumeric(ArrColumns(0)) Then
'Its a numeric data
ElseIf IsDate(ArrColumns(0)) Then
'Its a date
etc..

Hope this helps

Thanks so much. It is very helpful. I will try out and thanks again.

John

[calvinko]

How to use VB to write CSV file ?

Could give some sample code ?

Thanks a lot
Calvin

[vb5prgrmr]

See the following in VB's help file on the index tab...

FreeFile Function
Open Statement
Input Function
Line Input Function
Print Statement
Close Statement

Good Luck