Avatar of nshusky
nshusky asked on

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
MySQL Server

Avatar of undefined
Last Comment
Argenti

8/22/2022 - Mon
Argenti

Just put ";" after the statements.

Hold on!

[dbo] ???

Are you sure this is MySQL??? It looks like MS SQL Server to me!
ASKER
nshusky

I'm in mySQL workbench.
ASKER
nshusky

I've requested that this question be deleted for the following reason:

I don't think this will work.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Argenti

Don't close the question yet. I'm working on it.
ASKER
nshusky

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
Argenti

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
nshusky

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
ASKER
nshusky

Hang on.  I added quotes to my full path and it appears to be trying to import
ASKER CERTIFIED SOLUTION
Argenti

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
nshusky

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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
nshusky

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?
Argenti

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
ASKER
nshusky

Thanks for your help.  I have to run today but I'll be back on this Monday and give it a try.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Argenti

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.
ASKER
nshusky

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;
Argenti

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
nshusky

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,
Argenti

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' ".
ASKER
nshusky

I terminated the line with /r/n and that seemed to work.

How would you do the variable?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Argenti

-- 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

ASKER
nshusky

Argenti,
I went to lunch so I'm just getting this.  Sorry for the delay.  Anyhow, thank you for all your help.
ASKER
nshusky

-- 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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Argenti

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.
ASKER
nshusky

No problem.  Thanks anyway.
Argenti

You're welcome.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.