Solved

mySQL COLUMNS TERMINATED BY DELETE Character?

Posted on 2015-02-22
11
121 Views
Last Modified: 2015-03-03
I am trying to load large text files that are delimited by DEL. Using Coldfusion I can do chr(127), but that doesn't seem to work here

LOAD DATA LOCAL INFILE "C:\\inetpub\\vhosts\\mysite.com\\dataload.data"
INTO TABLE tbl_data
COLUMNS TERMINATED BY '\0x7F'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Open in new window



I have been searching and trying a number of things that look like they can be DEL characters. Does anyone have any suggestions for the COLUMNS TERMINATED BY line?
0
Comment
Question by:theideabulb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40624574
Are you absolutely sure your columns are delimited by DEL - That seems really odd and something I've NEVER come across.

I might suggest you do a full search and replace using a decent text editor and use something more acceptable before trying to import into your database.
0
 

Author Comment

by:theideabulb
ID: 40624865
No this is 100% the case.  I have been parsing it through CF for years, but this is MUCH faster.   I need to do these files all the time time, so this is indeed what I am looking for.  I just need to know the character/code/terminator for DEL for this use.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40624888
OK. No idea!!

Any chance you can post a sample of the DEL delimited file. May help us to figure it out. We can at least view the raw data which will give us something to go on.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:theideabulb
ID: 40624894
Sure, here you go.  It is attached
0
 

Author Comment

by:theideabulb
ID: 40624896
Lets try that again, i don't think it let me upload something that had a .data extension
0
 

Author Comment

by:theideabulb
ID: 40624897
Ok, that didn't work either.  Lets try this. I uploaded it to box.com

https://app.box.com/s/jr1fge054svhklrgydzo4i7wwylzf1l9
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40624935
No. Still no go...Sorry.

Maybe someone else has come across this before, but I certainly haven't (and I hope I never do!)

Whoever thought that was a good idea needs a serious talking to ;)
0
 

Author Comment

by:theideabulb
ID: 40624938
Thanks for trying.  I agree with you and that is why I am here asking.  It was actually quite simple doing in ColdFusion by using chr(127), but its slow, especially when you need to process 120+ files which is a few hundred thousand lines.  mySQL loader is MUUCCHHHHH faster :)
0
 

Accepted Solution

by:
theideabulb earned 0 total points
ID: 40624940
Here I found the answer

COLUMNS TERMINATED BY X'7F'
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 40625291
Good job. I'll try and remember that if I ever come across such a file :)
0
 

Author Closing Comment

by:theideabulb
ID: 40641309
I found my own answer.  Thank you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Query Using Up Memory 6 61
Concat multiple records into one line 3 72
MySQL HA and DR solution. 5 38
Export Data from MySql Using PHP 16 67
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 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