Import Mapping from .csv

[Nothnless]

Hello, I have a database that auto imports excel worksheets via macro button. However, some of the columns in the worksheet have alphanumeric characters which messes up the Access tables during the import. My work around is to save the worksheet as a .csv file, map the format I want for each column, and have Access import the .csv file. My problem is, I dont know how to import map a .csv file. Can anyone help map the file in the link below so Access can import it correctly? Its just a test file with 4 columns and 4 rows, but it will help get me started. Thanks!

https://1drv.ms/u/s!AmSI_zScjZJQkHMfccACXzKzRXvS

 

[bobsan42]

do you import it in a new table or in existing one?
If you import in an existing table - all that has to match are the column/field names. The data type is set in the access table - you cannot set it in the csv.
During import in a new table Access tries to guess the data type based on the first few lines of text in csv.

if some data does not match the set format for the field or cannot be converted - it will be discarded.
still I cannot understand - if the data in the xls table doesn't work well with your tables then the same problem would appear while importing from csv.
Unless you manipulate/correct the data first. And you could still stick with the xls format.

 

[Nothnless]

do you import it in a new table or in existing one?
If you import in an existing table - all that has to match are the column/field names. The data type is set in the access table - you cannot set it in the csv.
During import in a new table Access tries to guess the data type based on the first few lines of text in csv.

if some data does not match the set format for the field or cannot be converted - it will be discarded.
still I cannot understand - if the data in the xls table doesn't work well with your tables then the same problem would appear while importing from csv.
Unless you manipulate/correct the data first. And you could still stick with the xls format.

Hi, I'm importing it as a table, not importing to an existing table. The problem is the alphanumeric field. I want to always import this field as text, how can I tell Access to do that? The data changes so sometimes the field will have just a number in the first row, sometimes all letters, and combinations. If it imports with the number at the top then Access formats the column as numbers and drops all the alphanumeric characters. I just want this column to always import as text but without using the wizard. The user wont be defining formats themselves. Is there anyway to do this? I was told creating an import map would work but I dont know how to create one.

 

[xenou]

Have you tried using the import text dialog from the External Data tab? You choose the data type as part of that import procedure. Or as noted, if you import to an existing table, the data type is already determined by the table definition.

 

[Nothnless]

Have you tried using the import text dialog from the External Data tab? You choose the data type as part of that import procedure. Or as noted, if you import to an existing table, the data type is already determined by the table definition.

The data changes in the worksheets, currently I have all my tables deleted on open then use a button to import the new worksheets as tables. If I already have the tables setup in access so that I can control the formatting is there some code you can share that would delete or clear all the information in the existing tables on open and then import the new worksheets to the existing table with my import button?

 

[xenou]

You said you were saving your worksheets as .csv files. So you aren't importing worksheets - you are importing text files. That was the solution I thought you arrived at.

 

[Nothnless]

You said you were saving your worksheets as .csv files. So you aren't importing worksheets - you are importing text files. That was the solution I thought you arrived at.

Yes, the worksheets export to .csv files on save. Then Access imports the .csv on open, but the format in the alphanumeric column still comes in as a number instead of text.

 

[xenou]

How are you importing the csv? Are you setting the field type when you do the import? Are you saving to an already created table with the proper field definitions?

 

[bobsan42]

better to just empty the table than delete it - when the data type is set up in the table definition access will not try to guess the data type and you can have it your way.
this will empty (delete all records from) the existing table:

currentdb.Execute "DELETE * FROM [COLOR=#ff0000]yourtablename[/COLOR]", dbSeeChanges

this will import the excel sheet:

 DoCmd.TransferSpreadsheet acImport, , cImportTable, "[COLOR=#ff0000]Excel file name with path[/COLOR]", True, "[COLOR=#ff0000]Sheet name[/COLOR]" & "!"

replace the text in red with your chosen names

 

[xenou]

Bobsan42, fyi related post: http://www.mrexcel.com/forum/excel-questions/944661-code-doesnt-work.html