Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


CSV Import and Transpose into Access

Posted on 2014-02-26
Medium Priority
Last Modified: 2014-03-26
I have searched the net and really haven't found and elegant solution for this. So i thought i would ask the experts.

I have a csv file that i receive from a mobile app. I have no control of the how the csv file is created. The csv file has 750 columns and two rows of data.

First row data are the field names ( or QuestionLabels) from the mobile app.

The second row of data is the answers to these questions.

the first row i am able to split easy enough. It's the second row of data that has me baffled. Here is why.

the csv file as you know is comma delimited, so when the answer itself has additional commas the split function thinks it's another field. So for example if i have data as such.

Replace Door, 350,"Install Trim, Locks and paint", 1,250.00, White

i get

Replace Door
"Install  Trim
Lock and Paint"

as my split data.

I am trying to transpose the two row into two columns in a temp table in my access database. I have posted my code below.

Any suggestions please.

Sub parse_csv()
Dim fd As FileDialog
Dim Question() As String
Dim Answer() As String
Dim x, y
Dim i As Integer, c As Integer, Ifilenum As Integer
Dim sFilename As String, sAText As String, sQText As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .InitialFileName = Environ("USERPROFILE") & "\Desktop\247\Pronto\"
    .InitialView = msoFileDialogViewList
    .AllowMultiSelect = False
    .Title = "Please browse for your *.csv data file"
    .Filters.Add "csv files", "*.csv"
    .Filters.Add "Text files", "*.txt"
    If .Show = True Then
        sFilename = .SelectedItems(1)
    End If
End With
If sFilename = "" Then Exit Sub

Set fd = Nothing

CurrentProject.Connection.Execute "Delete * From TempCSV"

Ifilenum = FreeFile()
Open sFilename For Input As Ifilenum
    Line Input #Ifilenum, sQText
        Question = Split(sQText, ",")
    Line Input #Ifilenum, sQText
        Answer = Split(sQText, "~")
        For i = 0 To UBound(Question)
            x = padQuotes(Question(i))
            y = padQuotes(Answer(i))
            If Not Right(x, 4) = "PICS" Then
                CurrentProject.Connection.Execute "Insert Into TempCSV (QuestionLabel, QuestionAnswer) Values ('" & x & "','" & y & "')"
            End If

        Next i
End Sub
Question by:jb702
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 22

Expert Comment

ID: 39891018
Maybe you can use  the built-in command to import text data?

    Const Spec_Name As String = "specname"
    Const Destination_Table As String = "destination_table"
    Const File_Name As String = "c:\somefile.csv"
    Const Has_Field_Names As Boolean = True ' <= make false if there are no headings in the file
    DoCmd.TransferText acImportFixed, Spec_Name, Destination_Table, File_Name, Has_Field_Names

Since your file is comma-delimited, you can probably omit the Spec_Name variable (or value)

so starting after you Delete * command, add this:

    Const Spec_Name As String = ""
    Const Destination_Table As String = "TempCSV "
    'Const File_Name As String = "c:\somefile.csv"
    Const Has_Field_Names As Boolean = True
    DoCmd.TransferText acImportFixed, Spec_Name, Destination_Table, sFilename, Has_Field_Names

Does that help?
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39891775

I thought about using TransferText as well, but won't that hit the wall on Access's limit of 255 fields per table?

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 39891912
The following works by using Excel to open and parse the CSV file, transpose it, and save it as a new flat file that gets imported.  Note that this will only work if the user has Excel 2007 or later installed: Excel 2003 and earlier has a column limit of 256.

Sub ImportCSV()
    Dim xlApp As Object 'Excel.Application
    Dim xlWb1 As Object 'Excel.Workbook
    Dim xlWb2 As Object 'Excel.Workbook
    Dim SourcePathAndName As Variant
    Dim SourcePath As String
    Dim SourceName As String
    Dim NewNameAndPath As String
    Const xlPasteValues As Long = -4163
    Const xlCSV As Long = 6
    Set xlApp = CreateObject("Excel.Application")
    SourcePathAndName = xlApp.GetOpenFilename("CSV Files (*.csv), *.csv", , "Select source file...", , False)
    If SourcePathAndName = False Then
        MsgBox "No file selected", vbCritical, "Aborting"
        GoTo Cleanup
    End If
    SourcePath = Left(SourcePathAndName, InStrRev(SourcePathAndName, "\"))
    SourceName = Mid(SourcePathAndName, InStrRev(SourcePathAndName, "\") + 1)
    NewNameAndPath = SourcePath & "Import-" & SourceName
    xlApp.DisplayAlerts = False
    Set xlWb1 = xlApp.Workbooks.Open(SourcePathAndName)
    Set xlWb2 = xlApp.Workbooks.Add
    With xlWb2.Worksheets(1)
        .[a1:b1] = Array("QuestionText", "QuestionResponse")
        .[a2].PasteSpecial Paste:=xlPasteValues, Transpose:=True
    End With
    xlWb2.SaveAs NewNameAndPath, xlCSV
    xlWb2.Close False
    xlWb1.Close False
    xlApp.DisplayAlerts = True
    DoCmd.TransferText acImportDelim, "ResponseImport", "tblQuestionResponses", NewNameAndPath, True
    Kill NewNameAndPath
    MsgBox "Done"
    Set xlWb1 = Nothing
    Set xlWb2 = Nothing
    Set xlApp = Nothing
End Sub

Open in new window

The following sample files may be helpful.  The CSV file is a phony import file.

LVL 49

Expert Comment

by:Martin Liss
ID: 39955440
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question