How to import txt file to Microsoft Access with fields as text, not numeric

Question

I'm trying to import a txt file into my Microsoft Access database but data is being treated as numeric instead of text.

My source file (named sourcefile.txt) is like this:

00120230200001083300091959212311
00120230200001220500220559212311
00120230200240082700083259212311
00120230200240220600224059212311
00120230205001224100234959212311
00120230300001190700193359212311

Around 80.000 rows of data, no fields or delimitation, all rows same length (32 chars). I want to import that data to a table named T_RAW_SWD with just 2 fields:

swd is the first field and has been set as text with 255 limit characters
id_swd is autonumeric and is the primay key

So the idea is to import all those data rows as text into field swd. I've tried just reading data using a query like this:

SELECT F1 AS swd FROM [Text;FMT=Delimited;HDR=No;Database=C:\Temp;IMEX=1].filesource.txt;

This code loads the data but as number, not text. I thought using IMEX=1 would make the trick but no. Here's the output:

Desired output:

I've read some sources but could not figure out a solution. Also, I've tagged the question with VBA because tried with different macros based on some sources like these ones:

excel import text file using vba in access

How to read txt files and import them into access table

VBA Code - Import a text file into an Access table - With Condition

Changing connection string in Excel messes up column data types

DoCmd.TransferText method (Access)

ImportExportSpecification object (Access)

The differents codes I've tested import the data also as numeric. I've managed to load data into queries or tables but as numeric always. I'm totally stuck about what to do to read data as text so i don't lose any info.

If I manually import the data with the wizard it works properly, but there are several files and this task will be daily so I'm trying to make it faster with SQL code invoked from VBA o VBA itself.

Also tried first creating manually with the wizard an import routine and call it from DoCom.TransferText using acImportFixed with no success.

Any ideas of what I'm doing wrong here? I got no errors on importing the data trough code, but i need it as text, not numeric.

By the way, I'm working on Microsoft Access 2007 and Windows 10, just in case.

Thanks in advance.

You need an Import Specification. Follow the links here. They are different (much older) than the ImportExportSpecification objects. — Andre
– Andre, Commented Jun 29, 2023 at 12:46
@Andre thanks to the links you've provided I've been able to understood properly where `ImportExportSpecification objects are located and managed to use it properly torugh VBA. Now everythings works. I will post a detailed answer but thanks — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Jun 29, 2023 at 13:11

Foxfire And Burns And Burns · Accepted Answer · 2023-06-29 13:39:21Z

Thanks to @Andre and the links posted in his comment I've been able to build a solution.

First step, in this answer we see how to locate all import-experto specifications. You can do it using a query with this code:

SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.*
FROM MSysIMEXColumns INNER JOIN MSysIMEXSpecs 
     ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID

First column hold the names of each specification in case you need to use one trough VBA like myself.

Second step, I've created manually a specification to import data using the wizard. You need to click on Advanced

Notice in the image above we can set up as we need to import data (i just need to 1 single field named swd chars 1-32). The tricky part here is the 2 buttons named Save As and Specifications. The second one is to load a saved specification but the first one will allow you save your custom spec with a custom name

I've created my spec and I've named it spec_swd. Once saved, you can rerun the SQL query posted at first to make sure you spec exists with the assigned name.

Once you've done all this steps with a few lines of code we can import the txt file properly:

Sub test()
Dim sourceFile As String 'source file to import
Dim spec As String ' specification name
Dim destTable As String 'destiny table

sourceFile = "C:\Temp\090623.txt"
spec = "swd_spec"
destTable = "T_RAW_SWD"

Application.DoCmd.TransferText acImportFixed, spec, destTable, sourceFile, False

End Sub

Everything works now using DoCmd.TransferText method (Access)

Collectives™ on Stack Overflow

How to import txt file to Microsoft Access with fields as text, not numeric

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related