UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Cancel The Import Of A Selected Csv File If The Heading Does Not Have A Specific Column?, Access 2010    
 
   
bsumesh
post Oct 3 2017, 11:27 AM
Post#1



Posts: 3
Joined: 3-October 17



I am trying to import a csv file into Access (the csv file name changes every time I do this, because I select the file using msofiledialogfilepicker). But if the importing file's first line (column names) does not have the ' item name' , then I want to cancel the import and exit sub. I like to get a message "file not imported due to missing column" . How can I do this using VBA? if applicable, how to use .selecteditems property in this ? I use Win 8Pro, Access 2010 32bit.
Any help greatly appreciated.
Thanks,
Go to the top of the page
 
doctor9
post Oct 3 2017, 12:24 PM
Post#2


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


bsumesh,

You can open the Excel file in an Excel instance, and look at the value in cell A1 of the first sheet before importing the data.

CODE
    Dim shtFirst As Object
    Dim xlApp As Object
    Dim strFilename As String
    Dim strValue As String

'   Replace this bit with the part where you let the user select the file, of course.
    strFilename = "H:\Ford Focus Payments.xls"

'   Create an Excel instance
    Set xlApp = CreateObject("Excel.Application")
'   Open the selected workbook in the Excel instance, and point to the first worksheet
    Set shtFirst = xlApp.Workbooks.Open(strFilename).Sheets(1)
'   Get the value from A1
    strValue = CStr(shtFirst.Cells(1, 1))
    shtFirst.Application.ActiveWorkbook.Close False
    Set shtFirst = Nothing
    Set xlApp = Nothing
    
    If strValue = "item name" Then
        MsgBox "Importing data..."
    Else
        MsgBox "I can't import that data.  The value in A1 (" & shtFirst.Cells(1, 1) & ") in the first worksheet is incorrect."
        Exit Sub
    End If

'   Add all of the code that imports the data from the file here.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
JonSmith
post Oct 3 2017, 12:38 PM
Post#3



Posts: 3,158
Joined: 19-October 10



Dennis, since its a CSV I'd always suggest keeping a thousand miles away from Excel.
Wouldnt treating it as text and reading the first line and using instr be quicker and more reliable?
Go to the top of the page
 
doctor9
post Oct 3 2017, 12:54 PM
Post#4


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


Jon,

That's a great idea. I missed the .CSV bit and focused on the bit about "column name" instead, and assumed it was an Excel file.

CODE
    Dim strFilename As String, strLine As String

'   Replace this bit with the part where you let the user select the file, of course.
    strFilename = "H:\TestData.csv"

    Open strFilename For Input As #1
    Line Input #1, strLine
    Close #1

    If Left(strLine, 9) = "item name" Then
        MsgBox "Importing data..."
    Else
        MsgBox "I can't import that data.  The value in A1 (" & Left(strLine, 9) & ") in the first worksheet is incorrect."
        Exit Sub
    End If

Hope this is acceptable,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bsumesh
post Oct 4 2017, 10:01 AM
Post#5



Posts: 3
Joined: 3-October 17



Doctor9, Thank you for your response. One of my issue is that my file path always changes . I pick the file using a msofiledialogfilepicker. I have a command button, and on click I get a file pick window. It is the 'picked file' that i want to check for a particular column before importing. My code below. I'd like to add your code to mine at the appropriate place. Could you advice?.
thanks

CODE
Private Sub CmdSelectFile_Click()

Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object
Set objDialog = Application.FileDialog(msoFileDialogFilePicker)

    With objDialog
        .AllowMultiSelect = False
        .SelectedItems(1) = Trim$(.SelectedItems(1))
        .Show
            If .SelectedItems.Count = 0 Then
            MsgBox "No file selected."
            Exit Sub
            ElseIf Right(.SelectedItems(1), 4) <> ".csv" Then
            MsgBox "You must select a csv (.csv) file.", vbCritical
            Exit Sub
                                      
            Else
                                
          Select Case MsgBox("File selected. Do you want to import the file?", vbQuestion + vbYesNo, " ")
                Case vbYes
                    DoCmd.SetWarnings False
                    DoCmd.TransferText acImportDelim, "ReferralsData Import Specification", "tempTable", .SelectedItems(1), True, ""
                                      
                If ObjectExists("tempTable") Then
                    MsgBox "Successfully Imported!", , "Message"
                 Else: MsgBox "Unable to import. File name is too long or contains invalid characters." & vbNewLine & "Please fix the file name and try again.", vbCritical, "File import error! "
                        
                        End If
                    Exit Sub
                    
                Case vbNo
                  Exit Sub
            End Select
        End If
    
    
End Sub
Go to the top of the page
 
doctor9
post Oct 4 2017, 10:11 AM
Post#6


UtterAccess Editor
Posts: 17,913
Joined: 29-March 05
From: Wisconsin


bsumesh,

I'm not sure how your code is working at the moment, as there's no "End With" in your code anywhere that I can see.

But basically you can add my DIM statements to yours at the top, and insert my code (starting with the "Open" line) as the new first part right after "Case vbYes". You'd need to replace "strFilename" with your ".SelectedItems(1)" reference. The bit of your code that actually does the importing would be in place of my message box saying that it's importing data, of course.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bsumesh
post Oct 5 2017, 11:56 AM
Post#7



Posts: 3
Joined: 3-October 17



Thanks so much!
Go to the top of the page
 
gemmathehusky
post Oct 13 2017, 07:40 AM
Post#8


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


is the first line a csv of column headers?

what I often do if I think a file may not be well formatted is to import it, and then iterate all the column headers to make sure that every column I expected to see is indeed in the file.

if you are doing it for one column, it's hardly any harder to do it for all of them. I actually have a table with the expected columns so I can amend the process without needing any code changes. Just add another column name to the checking table.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:21 PM