Import Excel field has lead space

[jsic1210]

Hello,

I'm trying to set up an import of an Excel file. One field in Excel, "Sales Tax" has a lead space, so " Sales Tax" is its Excel name. Since I can't name a field in Access starting with a space, the only way I know to import properly is to manually remove the space from the Excel file. While this isn't actually that difficult, I'm trying to avoid this manual step if possible.

Thanks!
Jeff

 

[vbaInet]

Depends where the file is coming and whether it stays in one place or not?

 

[jsic1210]

The file comes from a billing system we use. The user will export an Excel output and save it to a specific folder under a specific name.

 

[vbaInet]

It just seems like too much effort writing code just to do one little thing.

If the exported file sites in a specific folder under a specific name then all you need to do is link to the file, create a query on the linked table, rename the field in the query and use the query to update your table.

 

[jsic1210]

Is there a simple way to just skip importing this field? It's actually not a necessary field. I just don't know a way to skip a field on an import.

 

[vbaInet]

I've just told you a way!

 

[jsic1210]

Well it will actually be different file for each month, so there will be several files to which it would need to link. And I've found that when a table/query is linked to an Excel file, it has slowed the database down.

 

[vbaInet]

So you're going to open it once a month then?

 

[jsic1210]

Yes, ideally once a month. There may be situations where it needs to be uploaded other times in the month, in which case it would delete the existing data for that month, then import.

I realize once a month is not often for the user to delete one space character, and it really isn't a big deal. I just thought if there was a quick way to solve this problem, it would make the process a little more seamless.

 

[vbaInet]

Yeah it's not a big deal. The amount of time it will take you to open a db, click a button and wait for it to open, edit, save and close the spreadsheet is about the same as opening the file and manually removing that extra space.

There's no quick way without writing code unfortunately.

Is there no way to correct the billing system's output?