Auto set required field size during CSV import to Access table?

[JJ]

Someone suggested Access as a middleman to get CSV files into FoxPro
quicky.

E.G. I have a 287,218 record CSV (Comma Delimited Value) file and am
not familiar with the maximum length of any of the fields. In this
case there are 20 fields, but I wouldn't always know that without
counting them.

My goal is to get this into a FoxPro .dbf.

Excel can't open this size file in its entirety.

Someone suggested using MS Access.

I could use FoxPro, it can't "IMPORT" a CSV file, only APPEND.

In FoxPro I would have to:

1) determine how many fields are needed (there are 20)

2) making sure each field is long enough to accomodate whatever length
data may be found in the CSV file etc.

3) create the FoxPro table

4) Do the actual append

Though I know very little about Access it was easy enough to create a
..mdb from the .CSV file, but it set the field lengths to 250 for all
fields.

At this point I figured I would give up and export to dBase IV, then
use FoxPro to whittle down the field sizes, but Access Export says
Field will not fit in record. There are 20 250 character fields and
287,218 records.

Is there a function in Access to automatically adjust the field
lengths to the minimum required length for each field based on the
actual data in the table,

either

at time of import from the CSV

or

after the fact ?
John "J.J." Jackson

 

[Nikos Yannacopoulos]

John,

I don't know anything about FoxPro, so I'll take your word for the
necessity to set the field sizes to the minimum required.

Since you already have the data in an Access table, I think the easiest
thing to do is just check for the field size required for each text
field, and set it up accordingly in the table design. At the point of
saving the changed table design Access will warrn that some data may be
lost, as it always does when you change a field size downwards in a
non-empty table; this does not necessarily mean it will happen, so if
you are confident you have the right field sizes go ahead.

Now, how to get the required field sizes: all you need is a query to
return the max of length per text field, like:

SELECT Max(Len([Field1])) AS Field1Len, Max(Len([Field2])) AS Field2Len,
Max(Len([Field3])) AS Field3Len
FROM MyTable

Modify the SQL expression above to accomodate all your text fields
(where I have Field1,2,3 in my example) and don't forget to change
MyTable to your actual table name. Then start making a new query in
design mode, add no table and click OK to the table selection window so
you are taken to the query SQL view; paste the SQL expression there, and
run the query; it will return the max of length per field.

Note that this process only applies to text fields; don't change Date or
yes/no fields, and be careful with numeric fields if you decide to
change them (look up data types in Access help).
Most importantly: back up your table before you try anything!

HTH,
Nikos