Open CSV file from MS Access (2 Viewers)

F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
Hi all,

I am trying to open a blank excel workbook from MS Access and then bring in the data from another excel file. I need to do this because of the file i need to open is a csv file and if I open it normally i lose leading zeros. I have tried the below code but i cant get it to work. Any help would be greatly appreciated.

Code: 
 Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Add
    xlApp.Visible = True
    xlApp.Sheets("Sheet1").Select
    xlApp.Sheets("Sheet1").name = strFileName

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\jsmith\Documents\002_Department.csv", Destination:=Range( _
"$A$1"))
.Name = "002_Department"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:24
Joined
Jul 9, 2003
Messages
16,280
Have you tried "Reading" the *.CSV file?

Sent from my SM-G925F using Tapatalk
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:24
Joined
Aug 30, 2003
Messages
36,125
Both posts moderated, now approved. Posting to trigger email notifications.
 
J

JHB

Have been here a while
Local time
Today, 12:24
Joined
Jun 17, 2012
Messages
7,732
You're missing the reference to Excel in the ActiveSheet code line.
Code: 
With [B][COLOR=Red]xlApp.[/COLOR][/B]ActiveSheet.QueryTables.Add(Connec
There could be more errors but then post back with the error message + in which code line it appear.
Where do you declare the variable strFileName and where do you set the value of it?
Code: 
 xlApp.Sheets("Sheet1").name = [B][COLOR=Red]strFileName[/COLOR][/B]
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,213
You mention doing this in 2 stages to prevent loss of leading zeroes in your CSV file. You can solve this by explicitly importing as text fields so they aren't being 'helpfully' converted to numbers.

Code: 
CStr(fieldname)

Then you can do it all in one step
 
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:24
Joined
Jul 9, 2003
Messages
16,280
Years ago I had a spreadsheet import problem where the wrong format was imported. The solution which worked for me was to add a row at the beginning of the spreadsheet.
In this row include values which represent the format you want the rest of the values in the columns to be imported as. In your case add text for example "xxxx"...

Sent from my SM-G925F using Tapatalk
 
F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
ridders said:
You mention doing this in 2 stages to prevent loss of leading zeroes in your CSV file. You can solve this by explicitly importing as text fields so they aren't being 'helpfully' converted to numbers.

Code: 
CStr(fieldname)

Then you can do it all in one step
Click to expand...

Can you expand on the above how do I import a file into access using this?

Kind regards,
Forrestgump
 
F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
JHB said:
You're missing the reference to Excel in the ActiveSheet code line.
Code: 
With [B][COLOR=Red]xlApp.[/COLOR][/B]ActiveSheet.QueryTables.Add(Connec
There could be more errors but then post back with the error message + in which code line it appear.
Where do you declare the variable strFileName and where do you set the value of it?
Code: 
 xlApp.Sheets("Sheet1").name = [B][COLOR=Red]strFileName[/COLOR][/B]
Click to expand...

Hi there,

I tried the above, unfortunately I couldn't get it to work. Any ideas?

Regards,

Forrestgump
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,213
I'm confused.

Your thread was asking about importing data from Access into Excel:

I am trying to open a blank excel workbook from MS Access and then bring in the data from another excel file. I need to do this because of the file i need to open is a csv file and if I open it normally i lose leading zeros.
Click to expand...

Now you're asking about importing into Access
forrestgump said:
Can you expand on the above how do I import a file into access using this?
Click to expand...

And you've also just created a new almost identical thread

As I'm unclear what to answer, I'll do the import into Access answer.

Here's a fairly complicated example in which:
a) the CSV file is first imported into a buffer table - ImportedCourses
b) the data is then modified to ensure correct datatypes & imported into the final table - Courses
c) the changes are logged to another table - LogOfImportedData
You can probably ignore this bit

Code: 
Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Courses", dbOpenDynaset, dbSeeChanges)
Set myset1 = mydb.OpenRecordset("ImportedCourses", dbOpenSnapshot)
Set MySet2 = mydb.OpenRecordset("LogOfImportedData", dbOpenDynaset, dbSeeChanges)

With myset1
If .RecordCount > 0 Then
    .MoveFirst
    Do Until .EOF

        If Not Nz(!CourseID, "") = "" Then
        strCourse = !CourseID
        myset.FindFirst "CourseID = '" & !CourseID & "'"
        If myset.NoMatch Then
            myset.AddNew
            myset!CourseID = !CourseID
            myset!Year = CInt(Nz(!Year, ""))
            myset!SubjectID = CStr(Nz(!SubjectID, ""))
            myset!ShowOnReport = -1
            myset.Update
            
            MySet2.AddNew
            MySet2!EntryDate = Now
            MySet2!ObjectName = "Course"
            MySet2!ItemReportID = 2
            MySet2!ItemText = "Course " & !CourseID & " added"
            MySet2!ItemAction = "Added"
            MySet2.Update
        Else
            myset.Edit
            If Nz(myset!SubjectID, "") <> CStr(Nz(!SubjectID, "")) Then
                strSubject = Nz(myset!SubjectID, "null")
                myset!SubjectID = !SubjectID
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Subject changed from " & strSubject & " to " & !SubjectID
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
            
            If Nz(myset!Year, 0) <> CInt(Nz(!Year, "")) Then
                intYear = Nz(myset!Year, 0)
                myset!Year = CInt(Nz(!Year, ""))
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Year changed from " & intYear & " to " & !Year
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
            
            myset.Update
        End If
        End If
        .MoveNext
    Loop
End If
End With

    WriteTextEntry strText4
    If IsLoaded("frmMain") Then Forms!frmMain.LblHelpText.visible = False

You should be able to find the relevant bits in the above
 
Last edited:
F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
Hi thanks for your reply

What I am trying to achieve is:-

open excel from access
then open the csv file in excel so i can edit

Does that make sense?
 
Minty

Minty

AWF VIP
Local time
Today, 11:24
Joined
Jul 26, 2013
Messages
10,371
I think you need to heed the advice given - which is to basically import the CSV directly to a temporary "staging" table, setting every field to text.
You can then manipulate that data exactly as you need to to get it into your final destination, in the format you need.

Ridders post shows how to do this.
 
isladogs

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,213
forrestgump said:
Hi thanks for your reply

What I am trying to achieve is:-

open excel from access
then open the csv file in excel so i can edit

Does that make sense?
Click to expand...

No - its illogical to do that

Assuming you want to end up with the data in Access (otherwise why else would you start from there), you should do it the way I've explained.
You can then edit the data in Access - either in the 'buffer table' before final import or after importing to the final table
 
F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
Unfortunately, I wont know how to do all the manipulation in Access that i need to do. Which causes other issues.
 
F

forrestgump

Registered User.
Local time
Today, 11:24
Joined
Aug 14, 2017
Messages
10
I found the reason for what was going wrong i needed to put xlapp in the destination part

Code: 
Destination:=xlApp.Sheets("Sheet1").Range("A1")
 
J

jpl458

Well-known member
Local time
Today, 03:24
Joined
Mar 30, 2012
Messages
1,038
isladogs said:
I'm confused.

Your thread was asking about importing data from Access into Excel:



Now you're asking about importing into Access


And you've also just created a new almost identical thread

As I'm unclear what to answer, I'll do the import into Access answer.

Here's a fairly complicated example in which:
a) the CSV file is first imported into a buffer table - ImportedCourses
b) the data is then modified to ensure correct datatypes & imported into the final table - Courses
c) the changes are logged to another table - LogOfImportedData
You can probably ignore this bit

Code: 
Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("Courses", dbOpenDynaset, dbSeeChanges)
Set myset1 = mydb.OpenRecordset("ImportedCourses", dbOpenSnapshot)
Set MySet2 = mydb.OpenRecordset("LogOfImportedData", dbOpenDynaset, dbSeeChanges)

With myset1
If .RecordCount > 0 Then
    .MoveFirst
    Do Until .EOF

        If Not Nz(!CourseID, "") = "" Then
        strCourse = !CourseID
        myset.FindFirst "CourseID = '" & !CourseID & "'"
        If myset.NoMatch Then
            myset.AddNew
            myset!CourseID = !CourseID
            myset!Year = CInt(Nz(!Year, ""))
            myset!SubjectID = CStr(Nz(!SubjectID, ""))
            myset!ShowOnReport = -1
            myset.Update
           
            MySet2.AddNew
            MySet2!EntryDate = Now
            MySet2!ObjectName = "Course"
            MySet2!ItemReportID = 2
            MySet2!ItemText = "Course " & !CourseID & " added"
            MySet2!ItemAction = "Added"
            MySet2.Update
        Else
            myset.Edit
            If Nz(myset!SubjectID, "") <> CStr(Nz(!SubjectID, "")) Then
                strSubject = Nz(myset!SubjectID, "null")
                myset!SubjectID = !SubjectID
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Subject changed from " & strSubject & " to " & !SubjectID
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
           
            If Nz(myset!Year, 0) <> CInt(Nz(!Year, "")) Then
                intYear = Nz(myset!Year, 0)
                myset!Year = CInt(Nz(!Year, ""))
                MySet2.AddNew
                MySet2!EntryDate = Now
                MySet2!ObjectName = "Course"
                MySet2!ItemReportID = 2
                MySet2!ItemText = "Course " & !CourseID & ": Year changed from " & intYear & " to " & !Year
                MySet2!ItemAction = "Modified"
                MySet2.Update
            End If
           
            myset.Update
        End If
        End If
        .MoveNext
    Loop
End If
End With

    WriteTextEntry strText4
    If IsLoaded("frmMain") Then Forms!frmMain.LblHelpText.visible = False

You should be able to find the relevant bits in the above
Click to expand...
Thanks
 
You must log in or register to reply here.

Similar threads

D
Solved Formatting Excel File from Access
Replies
5
Views
727
Eugene-LS
Eugene-LS
D
Solved Creating Excel Files from Access
Replies
9
Views
730
arnelgp
arnelgp
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,046
Cotswold
Cotswold
L
Solved Extract excel from access
Replies
4
Views
706
Leo_Polla_Psemata
L
L
Export data from Access to Word with VBA
Replies
12
Views
976
arnelgp
arnelgp

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top Bottom