Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

CSV With Data to Large for MS Database software??

Posted on 2006-05-26
26
Medium Priority
?
839 Views
Last Modified: 2008-01-09
Hi ,

I have an online survey program that generated output into a csv. The problem is thatthe csv is too large in the length of the rows/columns/column start positions to be imported into Access, MS SQL Server, or even excel

I just got the error from Access: 'field 107 contains a start position of 33090 and the maximum start position allowed is 32767.'

How do I fix this?
0
Comment
Question by:Frylock
  • 9
  • 7
  • 5
  • +2
25 Comments
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16767227
Break up the file into 2 or more files.  It's a CSV - TEXT - file - it should be easy to break up.
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16767244
SQL can handle MANY more rows than that.  And I'm fairly certain access can too... but access is limited to 2GB in size - SQL is not - there's no realistic limit in SQL.  The import tools might be limited, but the actual database is not.
0
 

Author Comment

by:Frylock
ID: 16767287
The error I get in MS SQL is:

'Cannot create a row of size 8196 which is greater than the allowable maximum of 8060'

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 97

Accepted Solution

by:
Lee W, MVP earned 2000 total points
ID: 16767306
You need to change some data types on the import.  varchar max size is 8000 characters, but the row max size is 8060 characters.  You can use a "text" data type to store more than 8000 character rows - text types only use 16 bytes (there are some issues with text types, for example, they are not as easily or completely searchable as varchar types).

How many columns and what are the sizes of each column?
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16767315
If you have SQL 2005 or download SQL Express then you can exceed 8060 characters.  (Note: i haven't worked with SQL 2005 based DBs yet so I only know that much from reading about them).


Also, HOW are you importing this - what import tool(s)?
0
 

Author Comment

by:Frylock
ID: 16767378
I was using theDTS import wizard. I think I figured out how to change the default data import types. Right now is says varchar255

What should I change it to?

I guess this is never going to make it into Access unless I break it up further.
0
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 16767394
Only you know your data - if it's a name field and you feel the longest name is 50 characters, then change it to VarChar(50).  Same for the other fields - you need to change it to what's appropriate for each field.  The ZipCode field (if you have one) may appear as VarChar(255) but in most cases imaginable, VarChar(10) will suffice.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16768002
max fields in a table is 255, how many columns u got?

some info for you regarding ms access limits
http://www.databasedev.co.uk/access_specifications.html

If u got less than 255 columns, its most likely your rows. You can download a utility to do that or write simple VB/VBA code which reads your large files and creates smaller files

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16768007
sorry, this assumes you are importing into MS access
0
 
LVL 9

Expert Comment

by:lojk
ID: 16788832
Can you post the first couple of lines from the file?
0
 

Author Comment

by:Frylock
ID: 17201031
No one gave me the right answer, which I found myself. You'd think a bunch of wizards would be able to figure this otu but they are all clueless.

The answer was to, as part of the DTS process, to hit the transform button and change the import columns to text.

Figuredit out myself.
0
 
LVL 9

Expert Comment

by:lojk
ID: 17201319
Hello Arthur,

leew touches on the answer with his early comment and should be allotted the points.

>>You need to change some data types on the import.  varchar max size is 8000 characters,
>>but the row max size is 8060 characters.  You can use a "text" data type to store more than
>>8000 character rows - text types only use 16 bytes (there are some issues with text types,
>>for example, they are not as easily or completely searchable as varchar types).

I have seen much of rockiroads work and i can vouch for the fact that neither he, I or indeed leew is 'clueless' and I'm sure that these valued contributors would find Frylocks unnecessarily antagonistic remark as offensive as I do.



0
 

Author Comment

by:Frylock
ID: 17202978
Leew did not tell me how to use the text data type. I had no clue you had to press the transform button.

In terms of clueless, I simply meant in regards to their ability to help me, a clueless newbie, with my problem because none of the broke it down so that I could understand or make it happen. Often with answers here, their own elite abilities make them skip over stuff that is obvious to them but unknown to the asker.

That is all.

If you're offended..... that's a 'you' problem.
0
 

Author Comment

by:Frylock
ID: 17202989
Ah - and I do agree that, upon review, Leew did give a good answer so I gave him credit. Thanks for pointing that out.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17203263
Frylock, all us people here who help out are volunteers
One does not know how much knowledge the questioner has. Sometimes some experts expand, othertimes they dont. But it should be down to you to ask more questions.
You also have to remember that not everyone knows everything inside out. The answers given by the questioners is to the best of their ability.
Ask another question in another area and chances are you will get a great answer. Now does that make people clueless?

I can see why Arthur is offended and I dont think its a "you" problem. People are genuinely trying to help you and a remark like "clueless" is like biting the hand that feeds you.
I have no problem with your remark, I just shrug it off if someone criticises. But you was decent enough to expand on your definition of clueless, and I believe you wasnt trying to upset people

I hope you dont take this post the wrong way. Im just trying to point out how it may feel to others.

I wish you luck in the rest of your venture!
0
 

Author Comment

by:Frylock
ID: 17203286
Why don't these guys get paid? Or at least get a chance to make some money?

I think I pay about $96 a year.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17203398
Clever eh, makers of this site know people like answering questions. Points sytem raised as a recognition type system. You can either pay or earn premium services. Lots of people obviously pay.
The EE q/a history here is very good.
0
 

Author Comment

by:Frylock
ID: 17203454
Right right, but I don't work for free. And the answers might be a little better if the answers got premiums or each point earned got them into a lottery.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17203508
Possibly. Though when I give answers, I usually try to give a good solution. Obviously if u get paid for it, then more time can be spent on it. But time is not always a luxury. Remember the way this site works is experts try to compete with each other to give a good working solution or somethng to help the questioner on their way.
I do this in my spare time (between the family time) and when bored at work. But its also addictive. If u like helping people then this site is for you.
I do a lot of PC support for friends and family. Would you consider that working for free? I dont charge them but in return I have had favours of them in their area of expertise, whatever that may be.
Just to let u know, I have had paid work thru this site so its not too bad.

0
 

Author Comment

by:Frylock
ID: 17203514
Actually, I have paid people found through this site for special project.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17203546
There u are then. EE acts two ways, u pay for a service, u can then hire people who u think are good
Experts help out, then maybe get paid work
0
 

Author Comment

by:Frylock
ID: 17203558
Eh. I suppose that is one way.
0
 
LVL 9

Expert Comment

by:lojk
ID: 17205546
For clarity, Rockiroads, was me that made the comment, not arthur.

Unfortunately the technology and patents behind EE seem so set in stone that the likelihood of contributors ever being financially rewarded is basically zero (as is testified by trawling very little of the service sections of the site). As such, any frustations with answers supplied or not should be aimed more at site management than volunteers who are trying to help, although I will accept that Frylock has subequently clarified his/her post.

There is a definte mark, whether intentional or not, in Frylocks Comment of irritation and I had admittedly been a little petty by deliberately displaying an approximately equal level of irritation in my comment (that was, after all predominantly a feedback post to the, again, valued CleanUp Volunteer Dude) to a question I'd had minimal involvement with, however please realise i neither intended nor implied any offence and Rockiroads has made all relevant points quite eloquently to conclude this matter.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17206266
oh, sorry lojk. I messed up after reading Arthur's post first! ooops.

This is what happens when u dont regularly clean your glasses. :)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17207520
And I could'nt care less if my name was being taken in vain.  After all, my Admin comment served its purpose, re-ignited the discussion, and lead to the proper awarding of the points.  Case solved, and mission accomplished. Next...

AW
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question