29
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 ...
26
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.
...
16
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 ...
answered May 2 '16 at 3:01
Ziggy Crueltyfree Zeitgeister
4,3431 gold badge8 silver badges20 bronze badges
13
You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Text; HDR=YES; Database=C:\Text',
'SELECT * FROM ...
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 ...
12
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 ...
11
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 ...
9
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
If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by
SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;
Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, given ...
8
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 from ...
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
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
It could be normal if there are newlines in certain text fields. Newlines are allowed when the value in the field is enclosed by double quotes.
And obviously that makes the number of lines in the file greater than the number of records.
Example :
$ cat file.csv
1,"ab
cd",2
3,"efgh",4
$ wc -l file.csv
3 file.csv
=> create table csvtest(a int, b text, c ...
6
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
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
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
REPLACE mechanically runs DELETE and INSERT. That may change the PRIMARY KEYs.
Here is something else you can do.
Suppose your table is called name_city and it looks like this:
CREATE TABLE name_city
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);
and you want to do the LOAD DATA ...
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
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'
...
5
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.
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
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 ...
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 │ ...
5
Just set the below in your session, as you would do in SQL*Plus:
set null "null"
Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - Preferences - Database - Advanced - Display Null Value As), but it affects the exported CSV.
5
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 ...
4
Assuming the psql command-line tool, you may use \copy instead of copy.
\copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file sharing in-between.
Under the hood, \...
4
According to the documentation, you can use SET statements to transform the data on the way in.
[SET col_name = expr,...]
The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:
SET latitude ...
Only top voted, non community-wiki answers of a minimum length are eligible
