Updating Column Data types within an existing vba code

[wao5012]

Public Function import_data_files()


Dim report_path As String, file_name As String

report_path = "C:\Raw Data"
file_name = Dir(report_path & "*.csv", vbDirectory)

Do While file_name <> vbNullString


DoCmd.TransferText acImportDelim, , Trim(Replace(file_name, ".csv", "")), report_path & file_name, True
file_name = Dir
Loop
MsgBox "Data files imported.", vbInformation


End Function

I have the above vba code to pull .csv files from a specific folder. I would now like to update the column data types so that all the information flows into the access table. I have a column called Project number and the data contained in the columns are numbers and text. when I pull them into access it only gives me the cells with numbers and not text. I need to convert the entire column to a text as it is being brought into access. Is this possible if so, how would i do this.

[Micron]

One approach is to link to the csv file as a table (or tables) then query what you need into a table(s) that has the proper field data types. So if the csv field is text (e.g. $1.00 as text) when you query that into the db table in the Currency field (because that would be the field data type you would have used) it should work. As long as the text conforms to the table field data type you should be ok. If a csv field is 1.25 (again, it is text) and you try to put in in an integer field it will fail because integers cannot contain decimal portions.

[madpiet]

I wonder if you can bring the data into Excel using PowerQuery, change the data types there, and then import into Access.

The more I use Excel, the more I think Access is stuck in the 1990s.

[NoellaG]

following method worked best for me: import all columns in an import table where all fields are text(max) columns and from there pump the data in the correct tables, where necessary using convert functions to get them in the correct data type.