Results 1 to 13 of 13
  1. #1
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55

    Special Delimiter characters

    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...

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  3. #3
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    It needs all 3 characters, not just the ;

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  5. #5
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    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.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just do this

    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
    the debug.print should show you your field contents separated by a comma with text markers (')

    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.

  7. #7
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    I'm not sure how to implement this code...

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  9. #9
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    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.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  11. #11
    cliff.clayman is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    55
    Here is some sample data.
    Attached Files Attached Files

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Removing special characters
    By crowegreg in forum Queries
    Replies: 3
    Last Post: 02-26-2014, 11:56 AM
  2. Search for special characters
    By davej311 in forum Queries
    Replies: 3
    Last Post: 11-20-2013, 02:35 PM
  3. Need to delete special characters
    By tlrutledge in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:10 AM
  4. Replies: 1
    Last Post: 12-14-2012, 01:10 PM
  5. Special Characters
    By orgelizer in forum Access
    Replies: 0
    Last Post: 03-20-2007, 08:24 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums