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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Importing Csv With More Than 255 Columns, Access 2016    
 
   
jsurpless
post Nov 8 2019, 02:32 PM
Post#1



Posts: 487
Joined: 21-December 03



I've got a CSV that unfortunately has considerably more than 255 columns from which I need to import a small subset of its data.

To date, I've simply been using .Readline to iterate through each of its lines and decide which columns I need to import.

As you can imagine, this can be pretty slow and I'm trying to find a way to improve its performance.

I've managed to develop a VBA subroutine to create the XML schema for a Saved Import but it only works with less than 255 columns, even if I'm not importing those columns. Merely including that column with 'SkipColumn="true"' is enough to count towards 255 columns.

Here's an example of my XML produced

CODE
<?xml version="1.0" encoding="utf-8" ?>
<ImportExportSpecification Path = "{MyFilePath}" xmlns="urn:www.microsoft.com/office/access/imexspec">
        <ImportText TextFormat="Delimited" FirstRowHasNames="true" FieldDelimiter="," TextDelimiter="" CodePage="437" AppendToTable="{MyTableName}" >
               <DateFormat DateOrder="MDY" DateDelimiter="/" TimeDelimiter=":" FourYearDates="true" DatesLeadingZeros="false" />
           <NumberFormat DecimalSymbol="." />
             <Columns PrimaryKey="{Auto}">
                        <Column Name="Col1" FieldName="Strategy" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col2" FieldName="StrategyContainedName" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col3" FieldName="Compound" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col4" FieldName="StategyTemplate" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col5" FieldName="Block" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col6" FieldName="Type" Indexed="NO" SkipColumn="true" DataType="Text" Width="12" />
                        <Column Name="Col7" FieldName="DerivedBlockType" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col8" FieldName="BlockType" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col9" FieldName="Locked" Indexed="NO" SkipColumn="true" DataType="Text" Width="12" />
                        <Column Name="Col10" FieldName="BlockContainedName" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col11" FieldName="IAName" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col12" FieldName="ABSDB" Indexed="NO" SkipColumn="true" DataType="Text" Width="12" />
                        <Column Name="Col13" FieldName="ABSGRP" Indexed="NO" SkipColumn="true" DataType="Text" Width="12" />
             </Columns>
</ImportText>
</ImportExportSpecification>


Thanks!
Go to the top of the page
 
isladogs
post Nov 8 2019, 03:42 PM
Post#2


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


Why not create a new worksheet in Excel e.g. using PowerQuery to contain just the fields needed for your data subset.
Then import that subset to Access

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
tina t
post Nov 8 2019, 03:45 PM
Post#3



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


well, i've never managed an Import (or link) that way; i always use the Import/Link specification wizard to create my specs. in the wizard, you can combine columns (fields) by adding/moving/deleting field break lines in the dialog box, or changing the Start and Width settings in the Advanced dialog.

so you might be able to reduce the "number of columns" in the text file by merging numerous "unwanted" columns into one column. i'm not sure how Access counts the columns in the text file, but it's worth a try.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
isladogs
post Nov 8 2019, 03:50 PM
Post#4


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


I may be wrong but think that will have the same limitation.
In other words, I think only the first 255 columns can be handled whether you use them or not.
Columns 256 and above will be ignored.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
projecttoday
post Nov 8 2019, 04:00 PM
Post#5


UtterAccess VIP
Posts: 11,281
Joined: 10-February 04
From: South Charleston, WV


How long does the .Readline method take? Did you try Line Input?

--------------------
Robert Crouser
Go to the top of the page
 
jsurpless
post Nov 8 2019, 06:32 PM
Post#6



Posts: 487
Joined: 21-December 03



QUOTE
How long does the .Readline method take? Did you try Line Input?


Since the CSV is an export that my users create from another application, how long it takes depends on how much of that database they are exporting. As such, it's hard to say exactly but let's say an average of 30-60 secs minimum?

QUOTE
Why not create a new worksheet in Excel e.g. using PowerQuery to contain just the fields needed for your data subset.
Then import that subset to Access


I hadn't heard of this feature, I'll have to look into it, although my preference would be to not have an additional file that is required.
Go to the top of the page
 
projecttoday
post Nov 8 2019, 08:26 PM
Post#7


UtterAccess VIP
Posts: 11,281
Joined: 10-February 04
From: South Charleston, WV


If the file is in the thousands (or less) then 30 seconds sounds a bit much. Like there's something wrong somewhere.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Nov 9 2019, 02:07 AM
Post#8


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


OK. I see 4 possible approaches to import your data.

1. modify your CSV after creation by selecting the required columns in Excel.... as previously suggested
2. extract just those columns when the CSV is created from the other 'database'
3. Try linking to your CSV instead of importing it. Then run a query to extract those columns (that part may not work due to the 255 field limit
EDIT: Just tested that. Linking also fails as soon as the file has more than 255 columns
4. What type of file is the other database? Can Access link direct to that file? If so, no need for a CSV file at all

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
GroverParkGeorge
post Nov 9 2019, 11:26 AM
Post#9


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

"...Since the CSV is an export that my users create from another application, how long it takes depends on how much of that database they are exporting."

I agree, that's the proper starting point, as Colin asked.

Your users create this CSV by exporting data from another database. That tells us a couple of important things.

The original source is, in fact, a database.

Users can export data from that database using whatever tool is provided for that purpose.

So, it seems to me that the trick here would be either to revise that export method so that it only retrieves fields needed for your report in Access, or to give your Access users direct links to that database so they can query against it using views that select only those needed fields.

Unfortunately, without knowing what that source database actually is, we can't offer real specifics about those options that might be available to you.

It also matters what kind of permissions you and your users have on that database, i.e. can you directly create views that you need, or do you have to have a DBA do that for you?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Nov 9 2019, 11:51 AM
Post#10


UtterAccess VIP
Posts: 11,281
Joined: 10-February 04
From: South Charleston, WV


Just to be clear - .Readline, which is the import of the export, is where it takes 30 seconds. (For how many records?) So the problem is in the import. It works just fine. It's just too slow.

--------------------
Robert Crouser
Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 11:56 AM
Post#11



Posts: 349
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Import the csv into a SQL Server table and link it to your Access frontend.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
projecttoday
post Nov 9 2019, 12:14 PM
Post#12


UtterAccess VIP
Posts: 11,281
Joined: 10-February 04
From: South Charleston, WV


Please confirm or deny post #10, jserpless.

Frank, will it handle > 255 columns?

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Nov 9 2019, 12:39 PM
Post#13


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


Yes it would. The limit is 1024 columns. See https://docs.microsoft.com/en-us/SQL/SQL-se...ql-server-ver15

However its an extra step which the OP already rejected when I suggested using Excel originally.
To my mind, the best solution is to create a query/view in the source database and link to that from Access. No CSV file needed

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 01:06 PM
Post#14



Posts: 349
Joined: 21-September 14
From: Tampa Bay, Florida, USA


@isladogs

I agree with you that creating a view in the source db and linking to that view is the best approach, but what if the only option available to the OP is the csv file?
This post has been edited by FrankRuperto: Nov 9 2019, 01:09 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
isladogs
post Nov 9 2019, 01:08 PM
Post#15


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


Then I'd suggest doing it in Excel as being a slightly easier option for many people.
But your suggestion would also work

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
FrankRuperto
post Nov 9 2019, 01:15 PM
Post#16



Posts: 349
Joined: 21-September 14
From: Tampa Bay, Florida, USA


Excel is definitely easier, but also has its limitations such as 64K rows, unless 64-bit Excel is used, whereas SQL Server Express (free) can handle up to 1024 columns and 10GB of data.
This post has been edited by FrankRuperto: Nov 9 2019, 01:15 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
GroverParkGeorge
post Nov 9 2019, 01:20 PM
Post#17


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


When the OP clarifies things, we can offer more concrete advice.

The problem here is the source of the data. If getting it directly from the source database IS an option, it's always better, but if not, then a traditional ETL process, probably involving a csv, is needed.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Nov 9 2019, 01:22 PM
Post#18


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


Actually its been over 1 million rows whatever the bitness since the xlsx format was introduced with Excel 2007.
Of course the OP may have 1025 columns ...or more which Excel can handle (limit=16384) smirk.gif
Swings and roundabouts really. We're both speculating on no evidence.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
GroverParkGeorge
post Nov 9 2019, 02:15 PM
Post#19


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Remind me. Do CSVs also support over 64K rows these days?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Nov 9 2019, 02:21 PM
Post#20


UtterAccess VIP
Posts: 1,893
Joined: 4-June 18
From: Somerset, UK


Good question. Apparently its the same as Excel which makes sense. That is 1048576 rows
See https://forum.openoffice.org/en/forum/viewt...f=9&t=82154

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 04:27 AM