Text and Number in same column (1 Viewer)

[gideonbot]

I am trying to import a file where the data can be either text or numbers in the same field i.e., a "Status Code" can be "2" or "2A".

I set the field type as "text" but it still only accepts the number format and throws out any text in the error report. Should I convert all input data to text before importing? If so, how?

Thanks

 

[DCrake]

When you run a bog standard import Access tries to determine the data type based on the first few rows of data. Make sure all your text fields. or at least the first one, is text type. However if you set up an import spec you can specify that this column is text type.

 

[dkinley]

Not sure if this is the same issue I run into from time to time importing serial numbers, but throwing in my .02.

Background: Sometimes I have to import +/- 35 .html files into an '07 database. Some serial numbers are just numbers and others are combination of numbers/letters. Even if my first import goes successfully (with the field set to text), latter imports will error when I attempt to append the next import to this table.

Theory: It seems that upon each import, Access will import into a 'hidden' table and then attempt to 'append' to the first table. I have no proof of this - it just seems that way because of the error.

The work around: I created an import utility that will create a master table first (setting the offensive field to text), then import each .html file to it's own table by specifying text for that field, and then appending all of the imported tables to the master table and finally destroying all of the imported table.

It's ugly and I think unneccessary, but it works.

This may also work if I remove the middle steps, but I only have do this once every 4 to 6 months so not bothering to test/change because what I have works.

Here is the import code ...

DoCmd.TransferText acImportHTML, sSpecification, sTableName, sFileName, True

Search the Help files and your favorite search engine for the 'TransferText' command - there is lots of great information.

The critical bit here is the 'sSpecification'. Here I use a variable because I have create different import specifications for the different .html files.

Again, do the homework by searching and creating an 'import specification' because I think therein lies your solution.

Hope that helps,
-dK

 

[gemma-the-husky]

i prefer to import csv's as you get total control

when you import an xl spreadsheet, access seems to guess, and you can't make it change its mind

i get my users to write "text" in row 2 of any spreadsheet column that needs to be text - and then use some known column to delete the formatting row from the import. always works.

----------
I havent imported html files, but i guess the same issue arises.

I actual prefer to import to a temp/local table - you can then run validation checks etc, before incorporating the data into the main dbs tables

 

[vbaInet]

i prefer to import csv's as you get total control

when you import an xl spreadsheet, access seems to guess, and you can't make it change its mind

I agree, I prefer csv's too and just plain text files. If it was an excel spreadsheet I would normally import it to a text file to get rid of all the "junk" before importing into Access.

 

[gideonbot]

Thank you all. I will give CSV files a shot.

 

[gemma-the-husky]

import the first one manually

at the bottom, there is an advanced tab

clcik this, and you can create a mapping for the csv

after you have this mapping you can use

docmd.transfertext with this mapping (specification) to automate the import.

 

[dkinley]

when you import an xl spreadsheet, access seems to guess, and you can't make it change its mind

Well said.

That is so true. I wasn't aware of that .csv would come through 'clean', I figured if Access did it with .html and .xls then it would be across the board.

Thanks for the tip and enjoy some rep.

-dK

EDIT: If I remember, you will be enjoying some rep if I can spread some around .... > >