943,660 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > Import and export mixed data from and to csv file in VBA6
Ad:
Permalink
Oct 13th, 2007
0

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

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Light Poster
jliu66 is offline Offline
30 posts
since Oct 2007
Permalink
Oct 14th, 2007
0

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

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
Reputation Points: 53
Solved Threads: 13
Junior Poster
hopalongcassidy is offline Offline
148 posts
since Oct 2007
Permalink
Oct 14th, 2007
0

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

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.
Reputation Points: 13
Solved Threads: 4
Light Poster
yello is offline Offline
30 posts
since Nov 2006
Permalink
Oct 15th, 2007
0

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

Click to Expand / Collapse  Quote originally posted by 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.

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
Reputation Points: 10
Solved Threads: 0
Light Poster
jliu66 is offline Offline
30 posts
since Oct 2007
Permalink
Oct 15th, 2007
0

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

Click to Expand / Collapse  Quote originally posted by 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.
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
Reputation Points: 10
Solved Threads: 0
Light Poster
jliu66 is offline Offline
30 posts
since Oct 2007
Permalink
Oct 15th, 2007
0

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

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
Reputation Points: 13
Solved Threads: 4
Light Poster
yello is offline Offline
30 posts
since Nov 2006
Permalink
Oct 18th, 2007
0

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

Click to Expand / Collapse  Quote originally posted by 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

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

John
Reputation Points: 10
Solved Threads: 0
Light Poster
jliu66 is offline Offline
30 posts
since Oct 2007
Permalink
Jul 2nd, 2009
0

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

How to use VB to write CSV file ?

Could give some sample code ?

Thanks a lot
Calvin
Reputation Points: 10
Solved Threads: 0
Newbie Poster
calvinko is offline Offline
1 posts
since Jul 2009
Permalink
Jul 2nd, 2009
0

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

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
Reputation Points: 156
Solved Threads: 296
Posting Virtuoso
vb5prgrmr is offline Offline
1,670 posts
since Mar 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Userform Name in variable
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Related to Forms Property





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC