Import excel spreadsheet to table and keep underscore in data

[T. McConnell]

Good evening,
I have an excel spreadsheet that I import to a table that has item numbers, some of the item numbers will contain an underscore in them. When the spreadsheet gets imported it creates a table with import errors which says conversion error and it happens on the ones with the underscore so it ignored the value and leaves it blank in the table. Is there a way to maintain the underscore when it's imported without having to manually update the data in the field?
Thank you in advance

 

[theDBguy]

You could try inserting a first row in your Excel data with underscores. Hopefully, Access will convert all the number columns into text. You can then delete the dummy record from the table.

 

[silentwolf]

Hi,

I am not sure how you import Excel to Access but maybe this will give you an idea of how to.

 

[arnelgp]

if you Know the Column name that is in the Worksheet,
you can Create a table before hand with Same fieldname.
make sure the Column with underscore is defined as Short
Text in your field definition.

then you import the worksheet and selecting "Append a copy of records...",
when you import and select your New table.

 

[ebs17]

I prefer the way of just linking the external table and then writing the data to the database table via append query. An import into a temporary intermediate table as an alternative only makes sense to me if fields in this table have to be indexed for performance reasons.

The query offers the possibility of directly performing transformations and conversions on values. You can install an inconsistency check and thus ensure, for example, that only new data records are imported.

INSERT INTO
   DBTable(
      ArticleNo, 
      [FieldList]
   )
SELECT
   CLng(Replace(T.ArticleNo, '_', '')) AS ArticleNo,
   [FieldList]
FROM
   [excel 12.0 xml;hdr=yes;imex=1;DATABASE=D:\AnyDirectory\AnyFile.xlsx].[Table1$] AS T

Because of the conversion error mentioned, I assume that the target field in the table has the data type Long, so a type conversion is necessary after removing the underscore.

 

[Gasman]

Could you not create an import specification to handle that?

 

[Pat Hartman]

As the others have already mentioned -- If the import is a spreadsheet, you don't have the option of using an import spec. That leaves you with two options.
1. modify the spreadsheet so that Access gets the column types right. People do this by appending a row with text in the columns that they want to be text. Or by opening the spreadsheet and using automation to change the data types of each column from general to text.
2. Importing into a predefined table. You don't need to use a local table for this or even one in the BE. Both options cause bloat. I create a separate database. I predefine the table and save the empty file. When I need to import new data, I copy the empty version of the template to my local directory which overwrites the previous version and import to that db. Then my links to the template allow my append query to work correctly. We can go into more detail if necessary.

 

[Gasman]

Sorry, perhaps I should have said Data Tasks?

 

[Pat Hartman]

The problem is that the link option does not give the ability to define columns for Excel. Access decides how to interpret the columns when you link to the data source.

 

[ebs17]

Access decides how to interpret the columns

More precisely: Jet does this immediately with access to the table before you can intervene. In the standard case, the first 8 values are checked for each column and then the data type for the column for the SQL access is determined. If you only have numbers (long) and an alphanumeric value follows much lower, this will generate an error.

By changing the registry entry from TypeGuessRows to 0, Jet can be made to check all column contents. Changes to the registry are always a bit problematic when you pass your application on to third parties. But you could also work here temporarily.

Alternative: As already written, you can use a saved import because it offers an import specification and you can set the column to text. This creates an import table, which has to be removed after evaluation (garbage data).
I would prefer to write temporary data to an additional temporary backend and link its tables to the frontend. At the end of the runtime, this backend would be terminated as a whole.

Maybe you get a CSV instead of an Excel worksheet. With the text file, a specification can also be used when linking.

 

[CJ_London]

a modification to the suggestion made in post #5 is to set hdr=No - so first row will be text so all rows will be text, then exclude the first row and convert values as required.

INSERT INTO
   DBTable(
      ArticleNo,
      [FieldList]
   )
SELECT
  ArticleNo,
   [FieldList]
FROM
(SELECT * FROM [sheet1$] AS xlData IN 'D:\AnyDirectory\AnyFile.xlsx'[Excel 12.0;HDR=No;IMEX=2;ACCDB=Yes] where F1<>"ArticleNo")  AS XL;

 

[ebs17]

hdr=No

I like the idea (the execution isn't quite finished yet). You then have to work consistently with the replacement field designations F1, F2, F3, ..., and you have to rely on the same field sequences in the worksheet.

 

[CJ_London]

If sequence can vary then use top 1 to get the field names. Just about to receive guests so will try to provide an example tonight

 

[NauticalGent]

Hi,

I am not sure how you import Excel to Access but maybe this will give you an idea of how to.

Who IS that guy...!?!

 

[Gasman]

I think that is @sonic8 ?

 

[NauticalGent]

I think that is @sonic8 ?

You couldn't see my tongue firmly embedded in my cheek

 

[Gasman]

Nope, took it at face value. Sorry.

 

[silentwolf]

Who IS that guy...!?!

Mr. Stiefel from Germany )
Not sure if he is on this forum.

But he has got some pretty good content on his website.

 

[T. McConnell]

if you Know the Column name that is in the Worksheet,
you can Create a table before hand with Same fieldname.
make sure the Column with underscore is defined as Short
Text in your field definition.

then you import the worksheet and selecting "Append a copy of records...",
when you import and select your New table.

@arnelgp, this is the same database you helped me with in the truck log one. I'm using the import spreadsheet function to pull in the excel document. I've labeled the column as text in excel, and the column in the temp and main tables are set to short text, however when it imported the the fields with the underscore in the tables are missing and just left blank. What's crazy is the unique ID field shows the text when it's formatted with the =TEXT... combination.