33
Both the approaches already suggested appear to be unnecessarily complicated.
Just use psql's built-in \copy command, which works just like server-side COPY but does a copy over the wire protocol to the client and uses client paths.
Because it's a psql backslash command you omit the trailing semicolon, eg:
\copy products TO '/tmp/products.csv' CSV ...
30
I hope you are using LOAD DATA INFILE.
Try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.
Other issue might be this, please visit the following links : MySQL LOAD DATA.
When you login in MySQL do like below,
abdul@xmpp3:~/Desktop/Jiva$ mysql -uroot -p --local-infile
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
...
24
Here's a shell script that can do what you want:
SCHEMA="myschema"
DB="mydb"
psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\
while read TBL; do
psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv
done
Make sure you set the DB and SCHEMA variables to your particular database and schema.
The wrapping psql ...
14
Check this example:
# cat /tmp/db.txt
id,col1,col2
1,23,"dsafsdf"
2,-1,"ghfdhg"
7,9,"strsdrt"
# mysql test
mysql> CREATE TABLE `test` (
`id` int(11) NOT NULL,
`mycol1` int(11) DEFAULT NULL,
`mycol2` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`);
mysql> LOAD DATA INFILE '/tmp/db.txt'
INTO TABLE test FIELDS TERMINATED ...
13
It is absolutely possible - the ever helpful documentation comes to the rescue, again:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
Which means you can do something like this:
COPY my_table (mt_id, mt_name, mt_created_by, ...)
FROM 'filename' [...]
What you cannot ...
12
In the end I coded a Python function import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types) that imports a CSV into a DynamoDB table. Column names and column must be specified. It uses boto, and takes a lot of inspiration from this gist. Below is the function as well as a demo (main()) and the CSV file used. Tested on Windows 7 x64 ...
11
You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files):
Linux or Mac OS X
free and open source
sudo pip install csvkit
Example: csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql
It creates a CREATE TABLE statement based on the file content. Column names are taken ...
10
SQL Server has always supported bulk inserting from CSV files, you just have to specify field/row terminators.
file.csv contains:
foo,bar,1
blat,splunge,2
Then we do this:
CREATE TABLE #foo(a varchar(32), b varchar(32), c int);
BULK INSERT #foo FROM 'c:\temp\file.csv'
WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ',');
SELECT * FROM #foo;
Results:
a ...
8
I found that sqlite3 custom init script can have meta-command as well as SQL statement:
#!/bin/sh
commandfile=$(mktemp)
# create temporary init script
cat <<EOF > $commandfile
.mode csv tablename
.import /dev/stdin tablename
EOF
# import
bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db
7
If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this:
cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin mycsvtable'
SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. ...
7
A Word on the Performance of Different Types of Functions
Generally speaking, Scalar Functions and mTVFs (Multi-Statement Table Valued Functions) are a bit of a built in performance problem. It's better, if you can, to use iTVFs (Inline Table Valued Functions) because their code is actually included in the execution plan (much like a VIEW but parameterized)...
7
You need to use bcp.exe or SSIS. Read Import and Export Bulk Data by Using the bcp Utility (SQL Server) and The Data Loading Performance Guide. Also, read How to Analyze SQL Server Performance to be able to tell why something is slow.
After you've read those, come back with a more actionable question, including structure of table, csv structure, ...
7
You can use powershell to fast import large CSV into sql server. This script High-Performance Techniques for Importing CSV to SQL Server using PowerShell - by Chrissy LeMaire (author of dbatools)
Below is the benchmark achieved :
5.35 million rows a minute for non-indexed tables and
4.35 million rows a minute for tables with clustered indexes.
The script ...
7
I, too, used to have problems exporting large result sets (7 - 8 GB) to delimited files. Neither SQLCMD nor BCP nor SSIS could handle dynamic result sets, dynamic text-qualification, adding a column header row, etc. So, I built my own tool to handle this. It currently exits as the DB_BulkExport Stored Procedure in the SQL# SQLCLR library (that I wrote), ...
6
See the mydata.csv file at bottom of the answer.
I created a table xy
CREATE TABLE `xy`
(
`fred` int(11) DEFAULT NULL,
`mary` int(11) DEFAULT NULL,
`billy` varchar(50) DEFAULT NULL
);
From the documentation here, I tried this
LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
...
6
You might look at PgFutter to help you out. It sounds like it would fit your use case well, because it creates tables for CSV formatted data based on the header line, and then loads the data into the tables.
6
If you need to export all schemas, here's the script
PGDATABASE="db"
PGUSER="user"
psql -Atc "select schema_name from information_schema.schemata" |\
while read SCHEMA; do
if [[ "$SCHEMA" != "pg_catalog" && "$SCHEMA" != "information_schema" ]]; then
psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" |\
...
6
There are some issues with this request:
What version and edition of SQL Server is the target system?
How much data is being imported? 10k, 10 Mb, more?
How many CSV files are there?
You have stated that handling of double-quotes is required, implying text-qualified fields and embedded text-qualifiers. Will there also be embedded field delimiters (i.e. , )? ...
6
COPY runs as the PostgreSQL server, which usually runs as NETWORKSERVICE on Windows. This is a local service account that does not have access to your user account's network login credentials for shared drives.
Use the psql command \copy, which reads the file from the client application, running under your user ID and with access to your shared drives, then ...
6
The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing.
CREATE TEMP TABLE baz AS
SELECT 1::int, '{"a":"b"}'::jsonb;
This is your exact sample data. Now we can test different settings..
# COPY baz TO STDOUT;
1 {"a": "b"}
COPY baz TO STDOUT DELIMITER ',';
1,{"a": "b"}
...
6
You can use pgScript:
DECLARE @i, @filename;
SET @i = 1;
WHILE @i <= 100
BEGIN
SET @filename = 'C:\\Program Files\\PostgreSQL\\9.5\\data\\' + CAST(@i AS STRING) + '.csv' ;
COPY location FROM '@filename' DELIMITER ',' ;
SET @i = @i + 1;
END
NOTE: You shouldn't have your CSV files under the PostgreSQL data directory. This directory is for ...
6
There's a problem with your first NOT EXISTS. Let's take a look at your current query:
INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
SELECT id.vnr
FROM dzp.contractid id
)
So, what's happening here is that the NOT EXISTS that you are using will return true only when the dzp.contractid table is ...
6
Your error message means the object can't be found in the context of the session where you're executing your BULK INSERT. This could be because...
The object does not exist and needs to be created. Try CREATE TABLE [Temp] ( ...
Also check for typos, did you mean to insert into Temp2 instead?
You may need to specify the schema name. Are you trying to insert ...
5
Another answer has already been accepted, but I disagree with that answer, because your question first requires an understanding of the meaning of the term "unsafe" and the phrase "considered unsafe" when it is used related to MySQL replication.
When speaking of the “safeness” of a statement in MySQL Replication, we are referring to whether a statement ...
5
I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).
Consult Oracle Utilities Guide for detailed info.
What follows is my sample of how you can update tables from flat files.
First ...
5
Something like this, checking every value for '', and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:
$ cat /tmp/test.csv
test1, test2, test3, test4
,,,
1,2,3,4
,,,
mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'...
5
To set NULL you just need your table to have allow nulls.
If you want to change it to some default value in case of nulls/missing values, you need to specify individual column names as below:
LOAD DATA INFILE "/home/mycsv.txt"
INTO TABLE inbound.master_data
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(name,...
5
You may use BULK INSERT for that:
CREATE TABLE #orders (
Column1 int,
Column2 nvarchar(max),
Column3 datetimeoffset
)
BULK INSERT #orders
FROM 'X:\orders.csv'
WITH
(
FIRSTROW = 1,
DATAFILETYPE='widechar', -- UTF-16
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
TABLOCK,
KEEPNULLS -- Treat empty fields as NULLs.
)
If you exported the CSV ...
5
My preferred approach to pass in multiple rows of multiple fields (i.e. not a simple delimited list) is to use Table-Valued Parameters (TVPs). The idea is that you will read the file line by line in your .NET code but stream the data either all at once, or if that is too much for one transaction, broken up into batches, into SQL Server using a TVP. The TVP ...
5
The copy command by default uses text format with tab delimiter. So only one thing you need is to escape backslashes:
copy onegram (ngram, year, match_count, volume_count)
from program 'sed ''s/\\/\\\\/g'' < /home/tims/data/ngram/test.tsv';
select * from onegram;
╔════╤════════════╤══════╤═════════════╤══════════════╗
║ id │ ngram │ year │ ...
Only top voted, non community-wiki answers of a minimum length are eligible
