Insert 8 lines at the top with specified values into CSV file using VBA-CodePudding

I have csv file which contains numbers and symbols in columns. I am exporting it into Access DB using VBA. The problem is that Access automatically trying to detect data type based on first 8 rows and first rows I have is numbers and then it ignores (gives error that can't convert data type actually) cells where I have symbols. Though I created table and specified all data types, still Access trying to convert it. So I would like to know if there any way to "say" to Access not to convert data type and insert all records as Short Text?

If not, then I would like to add at the begining of the CSV file 8 lines with "Dummy" word in every cells.

Let's say I have table which looks like:

Col1 Col2 Col3
1     2    3
2     3    4
..   ..   ..
r1   r2   r3

and I would like to make it like

Col1  Col2  Col3
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
Dummy Dummy Dummy
1     2    3
2     3    4
..   ..   ..
r1   r2   r3

Any way to achieve it?

CodePudding user response：

Use External Data import wizard to create an import specification. At the end of the wizard dialog, click Advanced to create specification. Reference the specification in DoCmd.TransferText method for subsequent imports.

Specification details are saved in hidden system tables MSysIMEXSpecs and MSysIMEXColumns. Can unhide system tables (can edit fields but that shouldn't be necessary) or use query:

SELECT MSysIMEXSpecs.SpecName, MSysIMEXColumns.DataType, MSysIMEXColumns.FieldName, MSysIMEXColumns.Start, MSysIMEXColumns.Width, MSysIMEXColumns.SkipColumn
FROM MSysIMEXColumns INNER JOIN MSysIMEXSpecs ON MSysIMEXColumns.SpecID = MSysIMEXSpecs.SpecID
ORDER BY MSysIMEXSpecs.SpecName, MSysIMEXColumns.Start, MSysIMEXColumns.Width;