Excel Numeric data to be imported as text in MSAccess

[Kobus-Dippenaar]

Good day to all,
May this post find you well.
I do need some expert advise.
Attached in the ZIPPED file is an Excel spreadsheet.
Columns A is all numeric, and needs to be represented in access as a text field.
Column B is a mixed format of dates entered and in some instances only plain numeric. I need to import this column as is into a text field in access.


I tried importing the excel sheet, but the data gets changed.
I tried to linked the Excel sheet but it also had an influence on the data.
In both cases the influence of change is NOT throughout. Hence my need to get this spreadsheet into access as is.


I would appreciate your help.


Thanking you in advance and Blessings.

 

[spikepl]

In Excel, select each entire column and set the format to text.

 

[Kobus-Dippenaar]

Thank you, I'll go and do!

 

[Kobus-Dippenaar]

@Spikepl,
I have done so, I tried to format Column A and B as text.
Column A turns to an Exponential number (something like 2345909+E12)
[Once imported into Access it remained the same.]
Column B was fine formatting it to text, BUT at line 72 the date changed to a 5 digit number.

Hence, I still have a problem, any suggestions? (To all out there!)


Looking forward to your reply,

Blessings.

 

[The_Doc_Man]

The problem is that Excel isn't as strongly typed as we would like it to be. I might try to build an empty table with fully asserted data types of text fields where you wanted text. Then I might append the import the spreadsheet into the explicitly formatted table (i.e. not into a new table).

If that doesn't work, I'm not sure just what I would try for round 2 but I'm sure it would involve extensive VBA programming to open the spreadsheet as an Excel Application Object for starters, followed by code to visit each cell.

If you were in a version of Access that supported the QUAD data type (which 2013 does, e.g.), you might be able to import the numbers and then convert them to text as a second step. But if you are on an older version of Access, that won't work. Even if QUAD could be used, my original suggestion of appending vs. importing to a new still would apply.

 

[Kobus-Dippenaar]

@Doc Man,
I am still exploring all avenues.
Linked as an excel spreadsheet, nor importing the spreadsheet into access doesn't work. Access converts fields depending how it recognises it, (My impression)
I thing I'll read it in as one complete text with delimiters and then unpacking it using VBA coding. Hopefully it will work as I will be using the DIM statement to define the individual receiving fields according the type, string, etc as needed.

Blessings.

 

[gemma-the-husky]

numeric as text:

I add an extra row 2 to the spreadsheet, and put the word "TEXT" in there. That forces the column to come in as text. Then I delete the row after the import.

otherwise access/excel examines the first few rows of the spreadsheet, and assumes the nature of the column based on that. For reasons like that, I much prefer to have a csv.

 

[Kobus-Dippenaar]

@gemma-the-husky,
Man! MAZELTOV!!!!! (Jewish) (Afrikaans slang: "LEKKER")
I have taken the first 10 records in the file, adding a "X" to the back of the field, whether numeric, number, or date/time it imports all as short-text.

Thanks,
Blessings.