Solved

CSV Import and Transpose into Access

Posted on 2014-02-26
5
1,135 Views
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
350
"Install  Trim
Lock and Paint"
1
250.00
white

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.Clear
    .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
0
Comment
Question by:jb702
[X]
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
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
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?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39891775
rspahitz,

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

Patrick
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 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)
    
    xlWb1.Worksheets(1).UsedRange.Copy
    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"
    
Cleanup:
    Set xlWb1 = Nothing
    Set xlWb2 = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
End Sub

Open in new window


The following sample files may be helpful.  The CSV file is a phony import file.
Q-28375713.csv
Q-28375713.mdb

Patrick
0
 
LVL 48

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

617 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