Importing txt file into SQL workbench

I am trying to import a pipe delimited txt file into a new table that gets created in SQL Workbench.  I have written the following routine but it is erroring out on the DROP TABLE line.  Any suggestions would be great.

DELIMITER '|'

CREATE PROCEDURE OnBoard_testImportScript ()

BEGIN
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Postal_Place_Test]') AND type in (N'U'))
DROP TABLE dbo.Postal_Place_Test
CREATE TABLE Postal_Place_Test
(ZIP5 Varchar (5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15))
-- Load data into table
BULK
INSERT Postal_Place_Test
FROM 'C:\Documents and Settings\Mike\Desktop\OnBoard\JC_zip-place-lkp_JUN13.txt'
WITH
(
FIRSTROW = 2, -- Removes the Header Row
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
)
END
nshuskyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
Just put ";" after the statements.

Hold on!

[dbo] ???

Are you sure this is MySQL??? It looks like MS SQL Server to me!
0
nshuskyAuthor Commented:
I'm in mySQL workbench.
0
nshuskyAuthor Commented:
I've requested that this question be deleted for the following reason:

I don't think this will work.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ArgentiCommented:
Don't close the question yet. I'm working on it.
0
nshuskyAuthor Commented:
I had re-written it to the following.  Additionally, I've been trying to make the mysqldbimport script work with a csv file but haven't had any luck with that either.

DELIMITER '|'

CREATE PROCEDURE OnBoard_ImportScript_Postal_Place ()

BEGIN
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[onboard_test].[Postal_Place]') AND type in (N'U'))
DROP TABLE onboard_test.Postal_Place
CREATE TABLE Postal_Place
(ZIP5 Varchar(5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15))
-- Load data into table
BULK
INSERT Postal_Place_Test
FROM 'C:\Documents and Settings\Mike\Desktop\OnBoard\JC_zip-place-lkp_JUN13.txt'
WITH
(
FIRSTROW = 2, -- Removes the Header Row
FIELDTERMINATOR = '|',
ROWTERMINATOR = ''
)
END
0
ArgentiCommented:
what is your MySQL Server version?
can you provide a sample import file?

I found there is a mysqlimport tool provided with the installation pack.
0
nshuskyAuthor Commented:
I'm on version 5.2 and that simple import tool is what I've been trying to get working since my 1st post.  

I've been trying the following but getting the error below:
mysqldbimport.exe --server=root:db9988@localhost:3306 --import=both --bulk-insert  --new-storage-engine --format=csv  C:\Documents and Settings\Mike\Desktop\OnBoard\place-amenities.csv

Error:
C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\utilities>mysqldbimport.exe --server=root:db9988@localhost:3306 --impor
t=both --bulk-insert  --new-storage-engine --format=csv  C:\Documents and Settings\Mike\Desktop\OnBoard\place-amenities.
csv
# Source on localhost: ... connected.
WARNING: New storage engine --format=csv is not supported on the server.
# Importing definitions and data from C:\Documents.
Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\utilities\scripts\mysqldbimport.py", line 183, in <module>
    dbimport.import_file(server_values, file_name, options)
  File "C:\Program Files\MySQL\MySQL Workbench CE 5.2.40\python\mysql_libs.zip\mysql\utilities\command\dbimport.py", lin
e 838, in import_file
IOError: [Errno 2] No such file or directory: 'C:\\Documents'
Unable to start mysqldbimport, please verify that your installation is correct
0
nshuskyAuthor Commented:
Hang on.  I added quotes to my full path and it appears to be trying to import
0
ArgentiCommented:
It works.
I happen to have MySQL 5.2 also installed.

USE `test`;

drop table if exists `Postal_Place_Test`;
 
create table `Postal_Place_Test` (ZIP5 Varchar (5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15));

load data local infile 'C:\\TEMP\\Csv files\\Book1.csv' replace into table postal_place_test 
fields terminated by ';' lines terminated by '\n'
ignore 1 lines;

Open in new window


5 row(s) affected Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

I created a simple dummy csv file, you can find in attachments
Book1.csv
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nshuskyAuthor Commented:
That's wonderful.  Mine is still trying but the file is huge.  Do you think yours will work on a pipe delimited or do I need to convert to comma delimited?
0
nshuskyAuthor Commented:
I see that you used the load data infile.  My fear with that command is that MySQL saves the record after every entry and I have well over 1 million records to import.  Any ideas about that?
0
ArgentiCommented:
You say the file is huge... how huge?

here is the LOAD DATA INFILE documentation

I suppose you might need to use the LINES TERMINATED BY '|' option
0
nshuskyAuthor Commented:
Thanks for your help.  I have to run today but I'll be back on this Monday and give it a try.
0
ArgentiCommented:
If you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. This option affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.
0
nshuskyAuthor Commented:
Argenti,
Below is what I have as a function.  It does not error out but no data is coming in either.   Suggestions today?


drop table if exists `zip_place`;
 
create table `zip_place`
(ZIP5 Varchar (5),
STATE2 Varchar (2),
STATE_ABBREV Varchar (2),
COUNTY5 Varchar (5),
COUNTY3 Varchar (3),
COUNTYNAME Varchar (25),
PLACENAME Varchar (28),
ZIP_TYPE Varchar (8),
MAILING_INDICATOR Varchar (1),
RESIDENTIAL_FLAG Varchar (1),
COMMUNITY_FLAG Varchar (1),
PREFD_PLACEKEY Varchar (6),
PREFD_PLACENAME Varchar (26),
ONBOARD_DATE Varchar (15));

load data local infile 'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.csv' replace into table zip_place
fields terminated by ',' lines terminated by '\n'
ignore 1 lines;
0
ArgentiCommented:
I am not sure, but I suppose you should not run that as a stored function because it will execute on the server, while your csv file is located on local machine (see your local file path).
I would suggest
1. either save your drop+create+load data inline as s cript file then run your script from a client or
2. try uploading your csv file on the server then call your function with the appropriate file path.
0
nshuskyAuthor Commented:
Well, I am trying to import now as a *.txt & pipe delimited so I don't have to convert to a *.csv.  That works except I'm getting a funny symbol in the last column of data.

Second, is there a way to get it to prompt for a file name & path when I run it locally?

Thanks,
0
ArgentiCommented:
Use a variable for the path to your filename inside your script.
It won't prompt you for a filename, but each time you open the script in MySQL workbench you will see it and you'll know it has to be filled with the right path. You can use comments to indicate that, for just in case someone else is running your script.

The odd character in the last column must be from the line terminator. You said you are using pipe to terminate lines, so please put that in your load command " lines terminated by '|' " or " lines terminated by '|\n' ".
0
nshuskyAuthor Commented:
I terminated the line with /r/n and that seemed to work.

How would you do the variable?
0
ArgentiCommented:
-- Please fill in the full Path to the local import file
set @PathToImportFile =  'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.csv';

-- Check the output for import errors and/or warnings
load data local infile @PathToImportFile replace into table zip_place
fields terminated by ',' lines terminated by '\r\n'
ignore 1 lines;

Open in new window

0
nshuskyAuthor Commented:
Argenti,
I went to lunch so I'm just getting this.  Sorry for the delay.  Anyhow, thank you for all your help.
0
nshuskyAuthor Commented:
-- Please fill in the full Path of the file for import.
Set ImportFile = 'C:\\Documents and Settings\\Mike\\Desktop\\OnBoard\\zip-place.txt';

load data local infile ImportFile replace into table zip_place
fields terminated by '|'  lines terminated by '\r\n'
ignore 1 lines;

I copied your code but it doesn't recognize the variable Importfile.  do I need to define it?
0
ArgentiCommented:
You are right. Unfortunately it won't recognize the variable name inside the load data statement. Here is what they say in the documentation:
The file name must be given as a literal string.
I'm sorry, I thought it can be automated like prepared queries, but it seems it's not possible.
0
nshuskyAuthor Commented:
No problem.  Thanks anyway.
0
ArgentiCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.