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
> Importing Excel To Access : Design Help Needed, Access 2016    
 
   
scoobster
post Oct 16 2019, 04:59 PM
Post#1



Posts: 132
Joined: 8-May 14



I'm trying to import a CSV file into an existing table using a text import. I do this because the format of the CSV will vary so I load into a table that has the matching columns but with field type set to text. This is so I can avoid Access looking ahead and guessing the format of the data in each column.

CODE
Sub testit()

Dim sBuildSQL As String

sBuildSQL = "INSERT INTO tblTable1Working_old (SELECT * FROM [text;fmt=delimited;hdr=true;database=C:\Excel Data\].[primary file.csv])"

DoCmd.RunSQL (sBuildSQL)

End Sub


I like to use this approach as it's very fast when the files can have 80-90k rows.

Could be a red herring but I do have a schema.ini in the same folder as the data but this is generally being ignored so I gave up on trying to create a table in the format I actually want, that's why I'm now importing to a pre-existing all text field table.

When I run this code I get the error "3134 - syntax error in INSERT INTO command'. I might be tired and missing the exact issue here. Any help gratefully received.

thanks
Go to the top of the page
 
June7
post Oct 16 2019, 05:20 PM
Post#2



Posts: 967
Joined: 25-January 16



Remove the parentheses from around the SELECT subquery.

If you want to avoid popup warnings, use CurrentDb.Execute instead of DoCmd.RunSQL.

This post has been edited by June7: Oct 16 2019, 05:21 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
tina t
post Oct 16 2019, 06:42 PM
Post#3



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


QUOTE
the format of the CSV will vary so I load into a table that has the matching columns but with field type set to text. This is so I can avoid Access looking ahead and guessing the format of the data in each column.

if you create an ImportSpecification using the ImportWizard in Access, you can set each field (column) to be exactly what you want, and create the table at runtime, or dump the data into an existing table.

if you want to filter or modify the data before it goes into a permanent table, you can do the same as above, except create a LinkSpecification instead. you can use the linked "table" in a query, same as a native table, manipulate the data as you wish, and then convert the SELECT query into an APPEND query to dump the data into an existing table.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
scoobster
post Oct 17 2019, 02:07 AM
Post#4



Posts: 132
Joined: 8-May 14



many thanks for your reply.

I've been playing with a linked Excel workbook but it's the same issue in that a column having both numbers and text is showing with '#err' for the text cells when I look at the linked data via Access. the very first value in one column that has both number and text is actually a text value starting with 'BR'. Other columns have date / time & blank cells. That's my biggest pain; not being able to get control of how data is imported.

So my current plan is to have a table created based on how the user specified: column names and simple data type of text, date, double, integer. Then the import should load the Excel into the data by some means and this is the part I'm really stuck on.

I can't use import spec because the workbook layout will vary. I have set typeguessrows to 0 and to 500000 in an attempt to get data loaded as text, by setting registry keys are Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel



Any help / suggestion is always welcome.

many thanks.
Go to the top of the page
 
tina t
post Oct 17 2019, 01:33 PM
Post#5



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


QUOTE
I've been playing with a linked Excel workbook

well, there's the problem, hon. you can't control Excel data the way you can control text file data, in an ImportSpecification or Link Specification. your first post cited using a CSV file, and i based my suggestions on that information.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
scoobster
post Oct 18 2019, 03:05 AM
Post#6



Posts: 132
Joined: 8-May 14



Thanks Tina.

the original post was correct in that I was trying to import a CSV using schema.ini file. the text import was super fast so I wanted to continue working this way, if at all possible. But I bailed on that because the structure of the table created by the import didn't reflect the structure I defined in the schema.ini. Plus I ran into issues with Access guessing the field types and getting it wrong most of the time.

Unfortunately I can't use an import specification for the CSV file type import because the structure of the import file is not static.

So my later post was really asking about options such as using a linked Excel instead and then copying data from that to a table. But even with a linked excel I see issues with the data such as #err when a text value is show in what Access 'believes' to be a numeric only field.

What is the general approach to take when importing an .xlsx type file that can have different structure and a mix of text / numbers in a column?

thanks again.
paul
Go to the top of the page
 
tina t
post Oct 18 2019, 03:34 PM
Post#7



Posts: 6,152
Joined: 11-November 10
From: SoCal, USA


hello, paul, i've never had to deal with the specific issues you're describing, hon. i'm sorry i have no further suggestions i can offer you. hopefully others are following this thread, and can make more useful suggestions. good luck with your project. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Phil_cattivocara...
post Oct 19 2019, 02:24 AM
Post#8



Posts: 368
Joined: 2-April 18



What do you mean with
QUOTE
... the structure of the import file is not static.
Does the structure changes from time to time? Are there different structures valid for the same period of import?
If the structure changes, you change your schema.ini
If you have to handle different structures in the same time, you could create different folder for every structure (schema.ini must be in the same CSV's folder)
Is there a way to recognise the structure before importing the file? For example from file name or first line or a particulare sequence of characters in a particular position.
Could you give some little real examples of CSV (without reserved data, of course)

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
dmhzx
post Oct 19 2019, 11:33 AM
Post#9



Posts: 7,112
Joined: 22-December 10
From: England


I've reached the stage where I usually bring in excel files a cell at a time using a couple of loops.

Two different approaches depending on whether an Excel cell is likely to have more than 255 characters
It also allows me to run specific UDF depending on the column I'm working with.

It take longer to run that transfer spreadsheet, but by the time I'd finished finding and tidying up the duff data, it take less time.

For example, I have come across Excel date like 31st November (which I will auto correct to 30th), dates like "TBA", and as mentioned cells with more than 255 characters, which transfer spreadsheet will truncate without telling you.

SO what do you want to do with the data if some rows have numbers in a column, and others have text? Convert them all to text? Put the number in one field and the text in another? Just ignore the text ?
ones There is no end to the rubbish data then can be thrown at you, and you need to somehow choose to what extent you manually correct it.
And if it's a CSV file that Excel has created, it may not comply with standard CSV conventions (Like having quote marks round some text data)

Let me know if you'd like any details.

David
Go to the top of the page
 
Phil_cattivocara...
post Oct 20 2019, 03:20 AM
Post#10



Posts: 368
Joined: 2-April 18



Do you receive CSV or xls(x) file? Or both?
Do you have any "power" to ask they pass you something standard, with a well defined structure?
QUOTE (dmhzx)
I have come across Excel date like 31st November (which I will auto correct to 30th), dates like "TBA",
What kind of [selfcensure] create date like those?
It seems like everthing is left to chance when building the file you have to import. It is impossible, at least for me, to give you any suggestion.
I understand there could be different structures to import but they must be always the same: first file type has this structure, second another one and so on. And somebody should give you the way to recognise what file type is, not "open it and guess", for every field in every line.

I continue thinking handling files as text (not necessary CSV) is the best way for your situation, with or without schema.ini, but... there should be more certainties.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
dmhzx
post Oct 20 2019, 04:11 AM
Post#11



Posts: 7,112
Joined: 22-December 10
From: England


Phil:
Re the 31st November.

Dates entered manually into Excel can easily be invalid.
Someone running through a lot of entries, having put in a lot of 31sts can easily just do the same with November (or indeed April or February)
Some just don't know there are only 30 days in November.
I've had manually created spreadsheet withe the same staff number for two different people, I've seen dates like 2109 instead of 2019.

Sometimes import spreadhseet will give you a list of which records it couldn't bringin with an error$ table

People make mistakes. If I can spot them and fix them during the import phase it saves time in the long run.
.
If the spreadhseet has been produced from corporate data, then you er in with a better chance, but i've still seen invoices with a due date a hundred years in the future.

The 255 character problem is also there when exporting from Access to excel, but I've found that using GetRows in my loop solves that one.

David
Go to the top of the page
 
Phil_cattivocara...
post Oct 21 2019, 02:08 AM
Post#12



Posts: 368
Joined: 2-April 18



QUOTE (dmhzx)
Dates entered manually into Excel can easily be invalid.
Ah! Now it is clear. I have always thought files were created by an external software, with automatic exportation for example.
Yes, people make mistakes, a lot of mistakes, every possibile mistake and when you have considered all, new mistakes come.
In my opinion you should consider to change the way the csv or xls(x) is created. You have Access: create an interface for data entry. If users do not have "full" Access, distribute your data entry project as accde with runtime. I know what you could answer: "They used to do that way and they do not want to change." If you can impose that dicision, do it. If somebody else can do that, talk about it, explain your reasons. But not say "So I can work less" but "Because this is the most efficient procedure" or such a thing.
Do you know what "_cattivocarattere" in my nickname means? "_badcharacter". My "gentleness" is worst than my English (read my signature), so do not listen to my suggestion in talking to other people.
This post has been edited by Phil_cattivocarattere: Oct 21 2019, 02:09 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
dmhzx
post Oct 21 2019, 02:58 AM
Post#13



Posts: 7,112
Joined: 22-December 10
From: England


Your English is perfectly understandable. Far better that any of my non English.

Even with some extracts from corporate systems, it may not be straight forward,
Recently i had to reverse engineer an excel file that had been created from two linked tables back into two linked tables, and change some of the descriptions in order to get compatibility with other corporate system data.

So if the data was a header, followed by three detail data, the excel file had three rows with the header data repeated in all three.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 06:39 PM