I am asking for help from the experts on this forum.
<o
> </o>
I need to import a CSV file into Excel and do a number of things with the values imported (that is not important right now). The problem I am having is with the import - with the number of fields and the order in which the fields appear in the CSV. In the CSV I need to import, there are 512 fields – more than excel can handle. That is o.k., because I don’t want all of them initially and not the problem I am having. I know how to browse to the file and import the data, skipping the fields I don’t need. The problem is the fields sometimes change position on the line. For instance, Field1 is sometimes first and other times 10th or something else, but it is always called Field1.
<o> </o>
So I would like to have the user provide a list of fields to get and then figure out where they are on the line so I can know which to import and which to skip.
<o> </o>
It seems like I need to read the first line into an array, search for the items I need, figure out the position they hold and then map that to my ActiveSheet.QueryTables.Add(…..).TextFileColumnDataTypes = Array(1, 9, 9,….) to know which ones I have to skip and which ones I have to read in.
<o> </o>
When using two files, Test1.csv and Test2 I want to be able to dynamically find Field10 and get the data in that column (0.92 in this case)
<o> </o>
<o> </o>
Any help is greatly appreciated.
<o> </o>
File One: Test1.csv
Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10
0.92,1.08,0.18,0.17,1.80,0.26,1.91,0.49,1.47,0.92
<o> </o>
File One: Test2.csv
Field10,Field4,Field6,Field2,Field3,Field5,Field9,Field8,Field7,Field1
0.92,2.08,0.38,0.11,3.60,1.2,0.91,0.5,1.7,1.2
<o
> </o>I need to import a CSV file into Excel and do a number of things with the values imported (that is not important right now). The problem I am having is with the import - with the number of fields and the order in which the fields appear in the CSV. In the CSV I need to import, there are 512 fields – more than excel can handle. That is o.k., because I don’t want all of them initially and not the problem I am having. I know how to browse to the file and import the data, skipping the fields I don’t need. The problem is the fields sometimes change position on the line. For instance, Field1 is sometimes first and other times 10th or something else, but it is always called Field1.
<o
> </o>So I would like to have the user provide a list of fields to get and then figure out where they are on the line so I can know which to import and which to skip.
<o
> </o>It seems like I need to read the first line into an array, search for the items I need, figure out the position they hold and then map that to my ActiveSheet.QueryTables.Add(…..).TextFileColumnDataTypes = Array(1, 9, 9,….) to know which ones I have to skip and which ones I have to read in.
<o
> </o>When using two files, Test1.csv and Test2 I want to be able to dynamically find Field10 and get the data in that column (0.92 in this case)
<o
> </o><o
> </o>Any help is greatly appreciated.
<o
> </o>File One: Test1.csv
Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10
0.92,1.08,0.18,0.17,1.80,0.26,1.91,0.49,1.47,0.92
<o
> </o>File One: Test2.csv
Field10,Field4,Field6,Field2,Field3,Field5,Field9,Field8,Field7,Field1
0.92,2.08,0.38,0.11,3.60,1.2,0.91,0.5,1.7,1.2
