Questions tagged [csv]

Questions on how to import into, extract data from, or exchange data between databases in CSV format (plain text using commas to separate values).

Filter by
Sorted by
Tagged with
16
votes
4answers
72k views

MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)` I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message ...
14
votes
2answers
36k views

Export remote Postgres table to CSV file on local machine

I have read-only access to a database on a remote server. So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permissions to create/save a file, so ...
14
votes
1answer
12k views

Can I query a tab-delimited file from SSMS?

Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere? I know you can BULK INSERT from a tab-delimited file using something like: ...
12
votes
5answers
36k views

Save Results as CSV *with* headers in SSMS

With SQL Server 2008 R2, when I save the results as a CSV there are no headers. I can work around this by copying and pasting with the "Copy with Headers" , or just grab the headers and paste them in ...
10
votes
2answers
12k views

How can I dump all tables to CSV for a PostgreSQL schema?

I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV. I'm aware of the COPY command but I'm not sure how to script something that will read all of ...
9
votes
3answers
20k views

Create a table from a CSV file with headers

I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties; "|" delimited. First row specifies ...
7
votes
4answers
25k views

Import CSV or JSON file into DynamoDB

I have 1000 CSV files. Each CSV file is between 1 and 500 MB and is formatted the same way (i.e. same column order). I have a header file for column headers, which match my DynamoDB table's column ...
7
votes
1answer
379 views

How many disk seeks does it take to write one row on SQLite?

As far as I understand, there can be one disk-seek per rotation of a harddisk, therefore I am interested to know how many disk seeks are needed for writing a row to an SQLite table, including locking ...
6
votes
3answers
32k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open file "...
6
votes
2answers
1k views

Very large SQL Server 2016 result sets (over 75 GB) to a CSV file?

What is the best way to get very large SQL Server 2016 result sets (over 75 GB) to a CSV file? The engineers need this output to look for correlations. The bcp route for a 73.5 GB file filled up ...
6
votes
1answer
28k views

Using mysql's load infile to only populate certain columns

I have a csv file with a lot of data in it and I want to upload it into a mysql table. However, I would like to keep one column (primary id) empty so I can edit it myself. In mysql's documentation it ...
6
votes
2answers
5k views

Select a CSV string as multiple columns

I'm using SQL Server 2014 and I have a table with one column containing a CSV string: 110,200,310,130,null The output from the table looks like this: I want to select the second column as multiple ...
6
votes
2answers
35k views

MySQL - CSV UPDATE (not INSERT) into existing table?

I have a table with the following columns: id | name | city The table has 1000 entries in it. I have a CSV file with a limited number of lines like this: id,city 34,Denver 45,Kansas City 145,New ...
6
votes
3answers
6k views

SQLite CSV import won't create a table automatically

According to https://www.sqlite.org/cli.html#csv, sqlite> .mode csv sqlite> .import C:/work/somedata.csv tab1 There are two cases to consider: (1) Table "tab1" does not previously exist ...
6
votes
2answers
32k views

Inserting all csv files into a table in MySQL

I am looking for a way to insert all my csv files into MySQL without having to write several LOAD DATA INFILE statements. I have many csv files I need to insert and the files themselves are very ...
6
votes
1answer
3k views

MySQL Import CSV with Multiple Field Separators

I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator. I am stuck ...
5
votes
2answers
963 views

How to import CSV file in SQL server 2008?

I am trying to import a CSV file in SQL Server 2008. BULK INSERT is a way to go but it is applicable for CSV from SQL Server 2014 onwards. What would be an alternative way to achieve this goal? Any ...
5
votes
3answers
826 views

Export to format compatible with SQL Server

I need to provide data in a format that could be imported into a Microsoft SQL Server database; however, I do not have Windows or access to a SQL Server instance. The data is currently in several CSV ...
5
votes
3answers
5k views

How do I disable escaping when using COPY FROM in PostgreSQL?

I have a large tab delimited file that I want to read into a table in PostgreSQL 9.5. It contains double-quotes and backslashes that I want to treat as regular characters. I think COPY FROM is the ...
5
votes
3answers
9k views

Import in postgres json data in a csv file

I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type. One line of my csv file contains something like 1,{"a":"b"} Suppose the ...
5
votes
1answer
11k views

Update oracle sql database from CSV

I tried google at first but no luck. Is it possible to update tables from csv file? I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
5
votes
1answer
4k views

Order the result of a comma separated string generated with STUFF

Given a schema like this: CREATE TABLE Foo ( Id int PRIMARY KEY, Position int NOT NULL, Title varchar(10) NOT NULL ); INSERT INTO Foo VALUES (1, 3, 'Title3'), (2, 10, 'Title10'), (3, 1, ...
5
votes
1answer
11k views

Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted

The following query imports 100,000 perfectly, but larger files nothing happens. No errors, no rows loaded. Any clues you can offer are appreciated. I spent hours looking into max file and memory ...
4
votes
2answers
7k views

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to?

Does the PostgreSQL COPY command have the option of choosing with fields to map the CSV columns to? The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am ...
4
votes
3answers
2k views

import 100 csv files into postgresql

I have a 100 csv files named sequentially 1,2,3....100.csv I copy the csv files with this script: COPY location FROM 'C:\Program Files\PostgreSQL\9.5\data\´1.csv' DELIMITER ','; I would to ...
4
votes
3answers
8k views

What's the most efficient way to load a csv file into SQL Server 2016?

Alright, so this question seems simple up-front. Well, there's a bit more to it than just loading the file. The entire story goes like this: For some reason, our client sends us what can only be ...
4
votes
2answers
2k views

Directly import a csv gzip'ed file into SQLite 3

I'd like to import a 15GB file comma delimited gzip compressed file into Sqlite 3 without having to use temporary files. I'd like to run a command like: zcat input/surgical_code.csv.gz | tail -n +...
4
votes
2answers
14k views

Copying CSV file to temp table with dynamic number of columns?

I am wondering if there is a way to copy a csv file into a temp table where the number of columns is unknown in the csv file. The DB software I'm using is PgAdmin III. I figured out that if I do know ...
4
votes
5answers
9k views

Bulk Insert - (0 row(s) affected)

I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the ...
4
votes
3answers
14k views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
4
votes
3answers
1k views

Import CSV file into Mysql with Multiple Delimiters/Field Separators

I'm trying to import a large csv file into Mysql. Unfortunately, the data within the file is separated both by spaces and tabs. As a result, whenever I load the data into my table, I end up with ...
4
votes
1answer
4k views

MySql Workbench Data Import .csv

Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records. After studying the .csv file I could see ...
4
votes
0answers
2k views

Can PostgreSQL's COPY create CSV with Locale-specific number formatting?

I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy. The encoding is solved, but Excel only ...
3
votes
4answers
170 views

Options for dumping Oracle queries to text files with no database server filesystem access

I've an application currently producing reports using UTL_FILE and DIRECTORY. We are migrating this application to an environment in which there will be no filesystem access on the database server (...
3
votes
2answers
11k views

How to create and populate a PostgreSQL table from CSV automatically?

I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile ...
3
votes
1answer
2k views

How to troubleshoot COPY FROM inserting less rows then expected?

I have a CSV (tab-delimited in fact) file with 7,590,051 lines, according to wc -l, which I want to import using COPY FROM. I removed the table's primary key, so it has no constraints. I ran COPY ...
3
votes
1answer
3k views

Why doesn't postgresql COPY command work with files in mapped drives

I have a problem copying CSV files from a network shared drive into postgreSQL, at present the fix is to copy the files to a local drive first, but it's another process in a chain which is becoming ...
3
votes
2answers
5k views

Loading data in mysql using LOAD DATA INFILE, replication safe?

I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here) Is ...
3
votes
2answers
18k views

MySQL Error when importing CSV with empty fields

I have CSV file with exported table, that has rows with fields that do not contain values. Columns are separated using "," and if fields have no values they look like that: ,,,,,, I tried to load data ...
3
votes
2answers
74 views

Generate CSV Email For Each UserID In Table

Good day all, I have a unique requirement where I need to generate a .csv file containing data for each userID. The result set that I have returned from my select statement is userID ...
3
votes
2answers
3k views

How can I output more than 256 characters to a file?

I am using the following command to write the output from a stored procedure to a file for further processing: DECLARE @shellCommand VARCHAR(2000) SET @shellCommand = 'SQLCMD -S ' + @@SERVERNAME + ' -...
3
votes
1answer
1k views

SSIS Repeated Header In CSV File

I am writing to a csv file in a Foreach loop. For each loop the code is writing the header so it appears repeatedly throughout the file. How do I ensure the header only appears once? I have tried ...
3
votes
1answer
16k views

import CSV into mysql table via command prompt (MySQL 5.6.25)

I'm trying to load CSV's into a mysql database with this command: LOAD DATA LOCAL INFILE '\Users\userName\Downloads\tableName.csv' INTO TABLE tableName FIELDS TERMINATED BY ',' LINES TERMINATED BY ...
3
votes
1answer
2k views

Importing CSV into multiple tables using PostgreSQL

I have a very big CSV file with the following format: TAG,TIME,VALUE as an example row: footag,2014-06-25 08:00:00.0,3400.0 I used to import it easily inside PostgreSQL 9.3 using the following: ...
3
votes
2answers
2k views

Excel Spreadsheet Import to MySQL

I want to add about 1000 records to a table that contains 31 fields. The worst part is those 1000 records are in the form of an excel sheet having some columns left unfilled. Is there any easy way to ...
3
votes
3answers
931 views

What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields?

I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its ...
3
votes
1answer
707 views

T-SQL: CSV->table pipeline with custom parsed numeric data, lookup values

Problem Wish to insert thousands of records into a table [Child] from a CSV file (and do this from tens of files into the same table). However, I want to A. substitute a lookup value into the table (...
3
votes
1answer
209 views

Dynamic paths for postgres file_fdw (csv)

I've got a postgres database which relies on the use of foreign tables to support the loading of large data sets based on user-provided (system sanitized) tables. I'd use COPY, but the tables loaded ...
3
votes
1answer
730 views

bcp error: date invalid date format

I'm a bcp newbie. I want to insert a large file. I'm having a hard time uploading datetime format. Table SQL: "Create Table dbo.my_test ( " + \ "transaction_id BIGINT ," + \ "transaction_no ...
3
votes
1answer
672 views

File size when extracting from SQL Server to CSV

I am attempting to extract a table to CSV using BCP from SQL Server 2014 and the size of the file once the data is extracted is actually larger than the size of the database itself. One thing to note ...