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