Field Data Type Issue (1 Viewer)

camollie · Oct 3, 2017

Hi AllI have a problem - I am importing an excel *csv file and the import works fine with one exception relating to 1 data file field.In the table I have the Data Type defined as "Short Text" the problem I have it would appear Access is trying to be clever on the input and thinks this particular field is actual Time/Date data. The field being imported relates to an account code which format is n-nnn-n (where "n" is a number), the problem is that this imports as a time/date.I have tried changing the Data Type to just about ever possible option but with no luck. The file does import ok when *.xlsx or *.txt, the problem is that I am trying to automate the whole process and the source data generated as *.csv and I don't really want to have to open each file and save the format as .txt or xlsxAny ideas/clues ???ThankdsAlex

isladogs · Oct 3, 2017

Either explicitly specify the data type when you export or exactly specify it when you import to an Access table using CStr.

The_Doc_Man · Oct 3, 2017

Ridders, your second solution won't always work if Access re-formats the string during the import because of the differences in date sub-fields. The format might change to n-nn-nnnn or something equally ugly.

Camollie, is it possible for you to somehow force quotes around your "n-nnn-n" field while it is still in the CSV file? That might trick the importer into changing the field type. The other possibility is that when you do the import, it SHOULD go into a dialog mode and allow you to specify the data format for each column that it sees before it attempts the actual import.

isladogs · Oct 3, 2017

When I import data from a CSV file, I normally import to a 'buffer' table first which only has text fields (as in the CSV file)
I then transfer from that table to the final table, modifying the datatypes as necessary.
If there's any chance of Access getting confused, I'll add CStr, CLng or whatever)
For example I have code like this with 2 recordsets (source & destination):

Code:

 myset!UPN = CStr(Nz(!UPN, ""))
myset!FormerUPN = CStr(Nz(![Former UPN], "")) 
myset![In LEA Care] = CBool(Nz(![In LEA Care], "")) '
myset![FSM Allowed] = CBool(Nz(![Eligible for free meals], "")) 
myset!Ethnicity = CStr(Nz(!EthnicityCode, ""))
If Nz(!DOA, 0) <> 0 Then myset!DOA = CDate(Nz(!DOA, ""))

etc ....

One extra step I know but, as a result, it ALWAYS works

camollie · Oct 4, 2017

thanks for the ideas - I will take this info and try to build a work around - I'll let you know how it goes fingers crossed

Pat Hartman · Oct 4, 2017

There are two solutions for handling .csv files.
1. Create an import spec that specifically defines each column's data type. Save the spec and reference it in the DoCmd.TransferText method. Do NOT use the newer import methods. They do not allow the flexibility to change your import spec.
2. Link to the .csv file and append to an existing temp table with all the columns correctly defined. This table may be a holding table or it may be the permanent table. If you append to a holding table, you can identify situations where Access tried to "help" you by creating a query that compares the linked table with the holding table and ensures that for each record, the contents of each field remains identical.

I actually use both methods. I create the import spec and then I delete existing rows from the temp table and I append using that import spec to the temp table.

gemma-the-husky · Oct 5, 2017

ridders makes a good point.

I tend to import csv's to designated blank tables, rather than new tables, so the data types are already established.

When importing spreadsheets though, to a "new table", access/excel can often decide that a column is numeric, when it should be text, and to get around that, I often add some text (such as the word "text") to row 2 of the spreadsheet - then delete the row after the data load.

The_Doc_Man · Oct 5, 2017

The "temp table" approach has worked for me before. I don't usually link to a spreadsheet, even if only as a dynamic thing. But in one or two rare cases, I was having so much trouble that I opened an Excel application object and read the file in a way that I could always ask for the text and could always convert it to the correct format. Though I have to admit than in both cases, there was a certain element of "free-form" layout in the sheet and I had to look for the data I needed based on surrounding context.

Galaxiom · Oct 6, 2017

Pat Hartman said:
There are two solutions for handling .csv files.
1. Create an import spec that specifically defines each column's data type.
2. Link to the .csv file and append to an existing temp table .

For the sake of completeness I will mention another two techniques.

3. The schema.ini file.

4. Read and parse the file line by line. This technique is very powerful and can deal with problems like wrapped records and page headers.

Pat Hartman · Oct 6, 2017

I've written my million lines of code and don't need the practice. Of course, if I can't use one of the Transfer methods because the file has inconsistent record formats, then I would write custom code. EDI transactions are no fun at all. they are pure tedium. There is nothing interesting about writing the thousands of lines of code they can require to read or write. So, yes, rolling your own is an option but probably shouldn't be very high on the list.

I forgot that Schemi.Ini was still supported. I'm not sure there is any advantage to them over the Transfer methods and there are at least two disadvantages. The file name is embedded and therefore not changeable without modifying the .ini file and you have to build them either manually or by creating your own tool that builds/edits variable format text files.

Uncle Gizmo · Oct 6, 2017

Another thing you could try is to have a dummy first record. Enter example records in this first record which are unambiguously the correct data type. My guess is MS Access uses this first record to decide which data types it is going to attribute. I used this method a few years ago, I have no idea if it will still work today!

Sent from my SM-G925F using Tapatalk

Pat Hartman · Oct 6, 2017

Access analyzes approximately the first 30 records to determine data type. For one time imports, adding a dummy row to force columns to be text works. You can't go the other way though and make a particular column numeric because if there are any conflicts in the first 30 rows, access will still make the field text.

The biggest problem with .csv files are long codes that are made up of numbers such as credit card numbers and any numeric code that might have leading zeros such as zip codes.

gemma-the-husky · Oct 7, 2017

It's also a bit tricky choosing a separator character that won't be in any (so-called) csv file. A comma itself may not work reliably. A lot of apps in my experience use the pipe symbol as a separator.

Date formats can be tricky. Offhand I think a (commonish) date format like 12-DEC-2016 will be treated as text during an import, although cdate("12-DEC-2016") works without any further manipulation.

Pat Hartman · Oct 8, 2017

The coma works fine as long as the text fields are delimited with double quotes which is the Access standard. There is no actual CSV standard, only a suggestion.

For CSV files, you can make an import spec and by pressing the Advanced button, you can set various options for date formats.

Field Data Type Issue (1 Viewer)

camollie

Registered User.

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

camollie

Registered User.

Pat Hartman

Super Moderator

gemma-the-husky

Super Moderator

The_Doc_Man

Immoderate Moderator

Galaxiom

Super Moderator

Pat Hartman

Super Moderator

Uncle Gizmo

Nifty Access Guy

Pat Hartman

Super Moderator

gemma-the-husky

Super Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread