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
0
votes
0answers
12 views

Postgres randomly fails silently on huge CSV import

With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query: COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER); The import fails ...
0
votes
3answers
939 views

convert csv to insert sql statements

I have a CSV file and I want to convert it into a SQL INSERT statements.But I could only find online converters but I would like to know can I create it manually ?
0
votes
1answer
31 views

CSV or DB is quicker to read several lines from a file with 1,5 billion lines

I have a program which would read several lines according to the indices of lines within a CSV file with 1,5 billion lines(using c fgets, or is there any tool in C faster than fgets?). Compare with ...
5
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 +...
6
votes
4answers
35k 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 "...
0
votes
1answer
2k views

Upload multiple csv file to multiple tables sqlServer

I have over than 200 csv files that each of them is diffrent table. I create now a new DB and want to upload them all so I will have all the table. I not accessable to old DB to backup in diffrent ...
3
votes
1answer
325 views

Update foreign key from csv file

I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current ...
0
votes
1answer
222 views

merge multiple fields of csv file while running mysqlimport command

I have a mysql table like this +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---...
0
votes
1answer
315 views

SSIS - Update Specific Cell in CSV

I have a pre-defined csv template. The header row begins on row 5. Prior to this I have a date value in cell B2. How do I update this cell value in SSIS?
0
votes
1answer
20 views

How often is Postgres parsing a foreign table based on file_fdw?

I have a foreign table defined with file_fdw, which reads the data from a CSV file. Postgresql parses the CSV file and converts the date for example from YYYYMMDD to YYYY-MM-DD. This works fine. My ...
1
vote
1answer
554 views

Exported CSV from MySQL doesn't appear in specified directory

I'm trying to figure out the syntax to export the results of a MySQL query to the desktop as a CSV file via SSH. Here's what I'm trying: SELECT * INTO OUTFILE 'C:\Users\Jim Smith\Desktop\zyzyz.csv' ...
2
votes
1answer
111 views

How do I bulk load data and still provide row-by-row feedback?

We want to allow our users to import CSVs containing many thousands of records to "pre-load" a particular table in our system. It's not strictly a one-time operation, it may be performed multiple ...
2
votes
3answers
6k views

how to import a huge csv file into Cassandra?

I am trying to import a csv file into Cassandra which is very long. These are food products: ingredients, nutrition, labels... It comes from Open Food Data. List information on food products: ...
0
votes
1answer
52 views

Mysql output to csv putting column names on the bottom

I'm trying to export the results of the query listed below to csv and have the column names in the first row. However, when I run the code and open up the csv the header is listed as the last row. I ...
2
votes
1answer
2k views

Compact Spool file from Sqlplus

I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed ...
1
vote
2answers
7k views

Load csv file to mysql using load data local infile

I need a simple way to load a csv file from my computer to the database (which is on a remote server). I'm trying to load the file using a php page that executes this code: $file = $_FILES['csv']['...
0
votes
1answer
851 views

Looping all csv files in local folder for data insert in MySQL db ( Both are local

Below is showing error "for f in 'C:\Users\Documents\TEMP*.csv DO LOAD DATA INFILE '"$f"' INTO TABLE TEMP FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES" DONE" ...
1
vote
1answer
134 views

InfluxDB batch insert only inserts the last line

I'm trying to insert some lines into a InfluxDB instance (running on Windows 7 x64) from both a .txt and a .csv file, but it's only inserting the last line on the file. I'm currently using the ...
0
votes
1answer
636 views

Dump MySQL table to csv file (utf8mb4_unicode_ci)

I have MySQL table with columns (character-set: utf8, collation: utf8mb4_unicode_ci) to be exported to a .csv file. mysqldump -u *** -p -t -T/tmp/ dbname tablename --default-character-set=utf8mb4 ...
0
votes
0answers
7 views

MySQL 8.0 appending unknown character to column

I'm inserting a CSV files into MySQL 8.0 using Python 3.7: bulk_insert_sql = "LOAD DATA LOCAL INFILE filename.csv INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' IGNORE 1 ...
0
votes
0answers
26 views

LOAD DATA INFILE NULL are enclosed not loaded

I have a CSV file that I exported from Oracle database. All the integer columns are not enclosed, but all the string columns are enclosed with " I'm using MySQL 8. So I used the following command to ...
0
votes
1answer
121 views

listagg in order and including a sequence of separator when missing any row of a sequence

Sorry for the long title but it's a little bit difficult to explain. We have a table | N rows | Atrib1 | Atrib2 | Atrib3 | Atrib4 | Final row | --------------------------------------------------...
0
votes
1answer
139 views

csv import fails without throwing an error

Ι am trying to import a csv file into an existing mysql 5.7 table. Here is the command (the db is in docker with bind mount for persistence) mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 ...
0
votes
2answers
24 views

Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files

As the title suggests I'm moving around a lot of data. This is in an environment with a 24 core CPU and 2TB of M.2 storage. My first question is: given I have 16GB of ram, does that mean it's ...
1
vote
1answer
69 views

Copy large PostgreSQL table to multiple csv files with limited row number

Is there a way, using a sql function or an already existing psql function, to copy a very large table into multiple csv files and specify the maximum number of rows allowed in the output files ? For ...
0
votes
2answers
52 views

How to automate the detection of the column type of a csv file for a table creation script?

I am new to pgAdmin and Azure database. I have a huge csv file with around 220 columns and I want to create a table out of it in pgAdmin4 to push it to Azure. However I don't know how to detect ...
1
vote
2answers
39 views

How to create a csv with SQLCMD which can be imported into another database system

I may be doing this all the wrong way so please point me in the right direction if I am missing something. I have an Ubuntu server with SQLCMD installed and a Windows Server with a MSSQL database. I ...
2
votes
1answer
157 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
0
votes
2answers
1k views

MongoDB CSV Export: One Array/Column & Array Index on rows, or vice versa

I have a MongoDB schema that looks like this: const batchSchema = mongoose.Schema({ _id: mongoose.Schema.Types.ObjectId, time: {type: [Number]}, tmp: {type: [Number]}, hum: {type: [Number]}, ...
4
votes
3answers
15k 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 | ...
1
vote
1answer
3k views

Import CSV to MySQL with long text fields containing line breaks

I'm trying to import a CSV file (';' separated) to a MySQL table. Normaly, I use a SQL like: LOAD DATA LOCAL INFILE 'local-path/file.csv' INTO TABLE tmp_table CHARACTER SET latin1 FIELDS TERMINATED ...
0
votes
0answers
30 views

Collect a column as CSV in GroupBy

I have this schema: Result Story: BILL_INFO has all bill headers. There is a business process called Deposit that occurs once a day: BILL_INFO are updated with a DEPOSIT_ID from DEPOSIT_INFO table ...
0
votes
1answer
72 views

How to add multiple query results into one csv file?

By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by ...
4
votes
2answers
15k 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 ...
0
votes
0answers
30 views

Import folder with Multiple CSV's into SSMS w/ out SSIS

I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS. Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables Folder contains about 30 ...
0
votes
0answers
8 views

Allow only import table in phpmyadmin

We build a wordpress site as a database interface in localhost (only). We are using a plugin WpDataTables to display tables, statistics etc. Is it possible to expose somehow only the import option of ...
0
votes
3answers
721 views

Extracting data from extremely large csv files [closed]

I have a 40gb csv file with over 60 million rows for data analysis. Each row has a unique identifier (some numbers). For example, the first row's unique identifier will repeat approximately 150,000 ...
1
vote
1answer
616 views

exporting a sql view to a csv file

Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice ...
0
votes
3answers
792 views

Leading zeros are truncated when I spool to CSV

I have spooled a file to CSV using sqlplus. Everything works great but there is one issue. I have mobile numbers which start with 0, for example 04589 and 02586. After the spooling is complete, 0s ...
0
votes
0answers
65 views

Errors with trying to run a stored procedure with local file output

I'm trying to run a stored procedure that I'm passing some variables to. The output is being writted to a CSV file on a local file share. The CSV file gets created, but I'm hitting some errors ...
-1
votes
3answers
345 views

Sub-select from comma separated list in WHERE clause

I have tables like so: TableA id id_link_list 1 1,2 TableB id info 1 blah 2 blah-more I'm struggling to include the TableA.id_link_list column (which is already a comma ...
0
votes
1answer
747 views

Importing CSV file into a PostgreSQL table

I am using Python with pandas to import a CSV file into a table in Postgres import pandas as pd import psycopg2 from sqlalchemy import create_engine df = pd.read_csv('products.csv', sep=';', ...
3
votes
1answer
727 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 ...
0
votes
0answers
24 views

RDDs, Maps and Apache Spark

I have been playing around with Apache Spark, firstly I learned PostgreSQL and I have a few queries that I needed to run on Spark. I managed to run them as SQL Strings in Spark SQL, but now I have to ...
0
votes
1answer
28 views

Best practices for storing multiple SQL tables per user (built from uploaded csv file?)

I have a few users who will be uploading a few types of predefined CSV files. I need to store these as sql tables to be able to run queries on this data for a particular user, however that means there'...
1
vote
0answers
16 views

Custom Export from power BI service

I'm trying to make a custom button that exports filtered tables to a csv and downloads them to my computer. I am aware that you can do this with the '...' button but this functionality is for other ...
0
votes
0answers
44 views

Can I force a LOAD DATA LOCAL statement to abort if an error is not loaded?

I am trying to load a CSV file into a MySQL table with the load data local statement. After loading it, the statement reports Query OK, 1030 rows affected, 7314 warnings (0.03 sec) Records: 1043 ...
0
votes
0answers
129 views

Writing CSV directly to a BYTEA column from PostgreSQL tables

Assuming these two tables: CREATE TABLE data (day DATE, kpi INTEGER); CREATE TABLE reports (csv BYTEA); Is it possible to (pseudocode): INSERT INTO reports (csv) (SELECT to_csv(SELECT day, kpi FROM ...
0
votes
1answer
20 views

Load flat file into a predetermined table

Scenario Table created Data in flat file loaded to the table but with errors. please advise how to fix it (a) 'address' is split to 3 columns causing mismatch between the source and destination (b)...
-1
votes
1answer
329 views

How to normalize CSV invoice data?

For flat data which comes in with fields like: name address status class version user mode CSV files are regularly received, with the above as typical columns. ...

1 2 3 4 5