I have a CSV file that I would like to import into my Access 2010 database. I am told that I have to use the following delimiter:
“;”
Can use this delimiter in Access? I can't seem to figure out how...
I have a CSV file that I would like to import into my Access 2010 database. I am told that I have to use the following delimiter:
“;”
Can use this delimiter in Access? I can't seem to figure out how...
Yes, access can use any text based delimiter along with tab/fixed width, etc.
You just have to set up an import spec and define the ; as the delimiter.
The very first screen of the import wizard lets you define whether the field is delimited.
The second screen allows you to select the delimiter (semi-colon is one of the pre-defined ones)
Just go through the rest of the import wizand and when it's set up correctly use the ADVANCED button to save the spec, then when you perform imports you refer to the import spec to do the heavy lifting.
It needs all 3 characters, not just the ;
really? so your line looks like
Firstfield";"SecondField";"thirdfield
because the " marks are usually indicators of text fields so if you field 1 was a number you would more ordinarily see something like
1;"Test Description";"Test Comment"
If your delimiter is actually three characters you should be able to read the file and re-write all the delimiters to something easier to handle (like a tilde ~) then import the converted file, or, alternately import the record after breaking each field into an array of fields but that is more clunky and likely to take longer if this is a large dataset.
Yes, the fields really do look like that. Trust me, it was not my doing. There are special characters in the data, so my IT dept. felt like this delimiter was necessary. I can't seem to find the right setup to be able to read the file into Access at all.
Just do this
the debug.print should show you your field contents separated by a comma with text markers (')Code:dim fs dim fIn dim sline dim aLineArray dim sSQLfields set fs = createobject("Scripting.filesystemobject") set fIn = "path and file name here" do while fin.atendofstream <> true sline = fIn.readline aLineArray = split(sline, """;""") for i = 0 to ubound(alinearray) 'this assumes each record *always* has the same number of fields sSQLFields = "'" & alinearray(i) & "'," next i ssqlfields = left(ssqlfields, len(ssqlfields) - 1) debug.print ssqlfields loop
this code assumes the fields are all text. if you want to define which fields are numeric either in tables or in the code or just rely on the 'isnumeric' function to figure whether a field should be numeric or not that's up to you but I'd stay away from it in case you get a date field in there too, so maybe test whether the field is a date first, then whether it's numeric, if it doesn't match either make it a text field. Once you have the sSQLfields spitting out the right format for the field types then you can make it part of a larger SQL statement to insert it into a local table.
I'm not sure how to implement this code...
it's a code snippet to give you the tools to cycle through your text file and append records 1 by 1. You'd just need a form or an event to kick it off (like an 'import' button on a form). You can substitute in dialog box commands if you want the user to be able to browse for the file they're importing or have a utility to scan for available files etc. It's a starting point not a total solution because I do not know anything about your project other than your stated problem of weird field delimiters.
And frankly, your original post says you have a 'csv' which goes back to my original issue. Have you actually opened the file with a text editor (word pad, note pad etc) and looked at the line construction, a csv is, necessarily, a comma delimited file which to me screams your fields are either all text or you have a 'text indicator' of " around text fields
for instance
1,"TestField1","TestField2"
if the first column is numeric
"1","TestField1","TestField2"
if all columns are text
If you had this
1","TestField,Split This way","TestField2
if this was in a 'csv' you would have four fields
1" | "TestField | Split THis way" | "TestField2
Open your .csv in wordpad and actually look at the lines I'd be almost willing to be it's actually comma delimited with " marks around text fields.
I've open the file in Excel and Notepad. It is delimited by these three characters ";" I was told that it had to be that way because of the data.
have you got an example of your data you can share because what you're saying in various posts to me is in conflict.
If you don't want to show some sample data (it doesn't have to be real) then you are stuck with parsing the text file as I showed with the filesystemobject commands and building a SQL string.
Here is some sample data.
I looked at your CSV file. I tried importing it using Access, but Access only allows 1 delimiting character. Plus, the end of line is just a line feed (LF) not a line fee and carriage return (LF CR)
So I opened the CSV file using NotePad++. Then did a replace, replacing every ";" with a comma.
Saved the file, then Access was able to import the CSV file, no problems.
I took the time to set every field to what I thought the data type should be.
The table created had 45 fields and 177 records.
If you import the data to a new table, you will have to edit the field names. There are field names with quotes, parenthesis and spaces.
If this is not a one time import, you might look at the "system file object" to develop a function to import the CSV file.
cliff.clayman.zip
Example Database
Make sure the database and the sample file are in the same directory I didn't make that part flexible.