Extract data from a comma delimited file using SQL

[cwajr]

Is it possible to extract data from a comma delimeted file
in an SQL statement and put the results into an access table?

If so How?

 

[scriverb]

The easiest way to do this is to link the comma deliminated text file to an access database. With the table now as a linked field with fields fully seperated you can create a query to take this table into a make-table query where you may perform criteria selects on the field and sort for the final table output.

Is this what you were looking for. There is really no way in SQL to parse out your table unless you know the exact position of each comma and and length of each field then use a Mid$ function to strip the data for each field. Very tedious and not necessary. Just use the ACCESS tools as described above and the job becomes easy.

Bob Scriver

 

[cwajr]

Thanks Bob,
I did that and it only brings in the first 19 fields. There are 78 fields I nee that are in the text file. Is there a way to get them all in? Am I missing a setting somewhere?

 

[scriverb]

I just now linked to a text file with 14,000 records deliminated with commas and had 38 fields. I then wrote a make table query with this linked table as input. This query created a table with all 14,000 records and 38 fields.

Just where is your process breaking down. In the Link process. After you Link check to see if all the fields are visible when you open the table. Or, is it in your make-table query. Let me know what you find.

Bob Scriver