VBA Access CSV file manipulation before import 1

[gmart]

I have a .csv file that has information i want to import. I tried importing the present csv into a table and moving data via Insert Into Select statement but couldn't get it to work. The easiest route it seems is to take the csv file and format it to match the table i want to move it to. Then the sql move will be easier. I have done similar file manipulation using Excel VBS but there are some built in features that are not present. Can someone show me some examples, or point me in the right direction.

 

[Andrzejek]

I see you have your csv file "buried" pretty deep.
Copy it straight to C:\ or just to C:\Temp\ and point to that location and see if your code will work.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Do you have the same user account on both your laptop and the host computer?

 

[gmart]

ok, so this is the exact code that worked on my laptop. Her computer i changed the file location to a C: drive folder as Andrzejek suggested and i still couldn't get it to work. Filename was CSVUpload and file name was od.csv.

 

[Andrzejek]

By "couldn't get it to work" do you mean you crash on this line of code?

[tt]Open "C:\CSVUpload.csv" For Input As #1[/tt]
or
[tt]Open "C:\od.csv" For Input As #1[/tt]

Even thou the specified file(s) do exist in this location?

What error are you getting?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmart]

neither
Open "C:\CSVUpload\od.csv" For Input As #1

ignore the comment talking about assuming test.csv is in c: drive. That's a copy paste thing from somewhere.

 

[gmart]

Basically, File not Found, and that was it.

 

[Andrzejek]

So what you are saying - you do have a folder on [tt]C:\[/tt] drive named [tt]CSVUpload[/tt] and in that folder there is a file named [tt]od.csv[/tt].

And the line of code:[tt]
Open "C:\CSVUpload\od.csv" For Input As #1[/tt]
gives you an error: File not found.

Is that correct?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[wp1]

Yes.

Here is code copied from the application computer.

Private Sub Command107_Click()
'Added by Greg Martin 08042017

On Error GoTo Err_Command107_Click

Dim strTextLine As String
Dim aryMyData() As String
Dim strSQL As String
Dim directory As String
Dim FileName As String
Dim i As Integer
Dim i2 As Integer
Dim currtoint As String
Dim i3 As Integer


    directory = "C:\CSVupload\"
    FileName = "od.csv"
    i = 1
    Open directory & FileName For Input As #1
    DoCmd.SetWarnings False  'TURN ERROR PROMPTS OFF
    Do While Not EOF(1)             ' Loop until end of file.
        Line Input #1, strTextLine   ' Read line into variable.
        aryMyData = Split(strTextLine, ",") 'Split text into array by comma
      If i <> 1 Then
        i2 = Len(aryMyData(8))
        currtoint = Right(aryMyData(8), i2 - 1)
        i3 = Int(currtoint)
        
        strSQL = "INSERT INTO Donations ([ID No], [Date of Donation], Amount, [Check #], [Deductable Amount], [Donation Type]) " _
            & " VALUES(" & aryMyData(22) & ", #" & aryMyData(7) & "#, " & i3 & ", " & aryMyData(23) & ", " & i3 & ",'Online')"
        
        
        DoCmd.RunSQL strSQL
         
     End If
      i = 2
    Loop
    Close #1
    DoCmd.SetWarnings True

Exit_Command107_Click:
    DoCmd.SetWarnings True
    Exit Sub

Err_Command107_Click:
    DoCmd.SetWarnings True
    MsgBox Err.Description
    Resume Exit_Command107_Click
    
End Sub

http://www.tek-tips.com/viewthread.cfm?qid=1778974

 

[wp1]

Sorry, this is gmart

 

[wp1]

attached is the windows explorer screen capture showing filename and file location

 

[wp1]

here is a picture of the error and the screen. Notice the 'Online Upload' button at the mid-right of the screen cap.

 

[Andrzejek]

I assume your [tt]Command107[/tt] is an [tt]Online Upload[/tt] command button, right?
If so, I would strongly recommend renaming it to something like [tt]cmdOnlineUpload[/tt]

The only thing I can suggest with this error is to start completely new, fresh Access and try to run just this code:

Open "C:\CSVupload\od.csv" For Input As #1
Close #1

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[wp1]

I will, next time I'm there. Thanks.

and for you:

https://thenextweb.com/dd/2011/12/1...ice-in-print-with-its-own-font/#.tnw_cFF6YY30

thought you would get a kick out of this

 

[gmart]

update:
I currently am running latest access on my laptop. opened up the database with no special conversion (database was created and maintained in Access 2003, and as i put in previous threads, it works. I setup another laptop with 2003 on it exclusively and brought the database over from the working laptop and the code worked on the new setup as well. So for some reason, i have not gotten in to work on the customers site box. This is crazy.

 

[Darrylles]

gmart,

This is the problem with data importing.
If it's a process that is to happen regularly, then you MUST have an agreement with the producer of the external file regarding standardisation.
The input file format MUST ALWAYS be AGREED and MUST NOT EVER change.

If it's a 'one off', then you must apply lot's of time and effort to ensure that it IS formatted perfectly, get the data in - then re-use it.

You mention that you are " running latest access on my laptop" and are experiencing problems.

IF you have managed to get the data IN to MS Access on one machine, then, the data transferral over to another machine (regardless of Access version) is a trivial affair.

Did you get the file data into MS Access on ANY machine?

D