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

61 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
0answers
3k 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
1answer
356 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 ...
2
votes
2answers
34 views

using SQLCMD to export/extract data withot trailing spaces

I am trying to extract data from some of my tables using sqlcmd utility as SSMS does not generate appropriate csv-s for me. My issue: all csv extract includes trailing spaces according to table ...
2
votes
0answers
49 views

How To Query Databases in Subject-Predicate Format?

I have csv data in this format: id fieldname fieldvalue -- --------- ---------- 1 fieldA A1 1 fieldB B1 2 fieldA A2 2 fieldA B2 If I upload this to MySQL, how can I ...
2
votes
1answer
132 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
1answer
215 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 ); ...
2
votes
0answers
467 views

pgAdmin export line overflow on large field

Background: I have a database of significant geographical locations downloaded from GeoNames open database. The create script for the table is as follows CREATE TABLE populargeonameswiki ( geonameid ...
2
votes
0answers
7k views

sqlldr error - Invalid number

I am trying to load the following csv file into a table: TIMESTAMP ; SESSIONID;PROXY_SESSIONID; ACTION;USERNAME ;USERHOST ;OS_USERNAME ;CLIENT_ID ...
2
votes
0answers
2k views

CSV File Into MYSQL Merging - LOAD DATA on Duplicate Key?

Hey Dba Stack Exchange, My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only ...
1
vote
0answers
30 views

Export and split QUERY results in multiple files

I would like to know if it's possible to export and split query results in multiple files. Let's assume I have a query returning 100 results and I want to save the results in 5 different csv files (20 ...
1
vote
0answers
19 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 ...
1
vote
1answer
34 views

How to relate LatLong and State columns on PowerBI

I have two separate tables, one with latitude and longitude columns (for locations in the USA), and the other with the US state abbreviation (TX, AZ, CA, etc). Is there a way to count the number of ...
1
vote
0answers
463 views

Which method gives better insert performance in mysql db table: CSV import or insert statement

I have combine data from two tables which results in 6727224 rows. I want to insert this data into another mysql db table. Which method should be very fast to insert this number of records. Insert ...
1
vote
0answers
510 views

Import Data From csv Into Postgresql WITHOUT COPY

I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run ...
1
vote
0answers
125 views

Postgres 9.4 importing csv file causes connection abort

I have very simple database which i would like to import data from a csv file, so far i tested with a file with a few rows, and it worked. So tried to upload the real data to the database, which ...
1
vote
0answers
1k views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. However,...
1
vote
1answer
576 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' ...
1
vote
0answers
91 views

Any way to mitigate the large server load during LOAD DATA LOCAL INFILE operations?

I'm using MySQL LOAD DATA LOCAL INFILE to load csv files, and have noticed that during these loads, server output speed to other users diminishes eg long page download times, logins timeout. The LOAD ...
1
vote
0answers
412 views

Importing CSV Files with Date Columns

I am working on a project that involves importing data via Apex's data workshop. Two of my columns contain dates in the format YYYY-MM-DD HH:MI:SS. Can anyone tell me how to get Apex to import these ...
1
vote
0answers
132 views

Join and aggregate result and insert blanks for missing appendants

Consider these two simplified tables: CREATE TABLE "sources" ( "id" serial NOT NULL, "name" text NOT NULL, CONSTRAINT "sources_pkey" PRIMARY KEY ("id") ); CREATE TABLE "values" ( "...
1
vote
0answers
546 views

Match unique id's on import- csv to sql

I have a csv file that I would like to import into a phpMyAdmin database (Wordpress). The CSV file is formatted as shown below: list-id | post_title | slider | ...
1
vote
0answers
425 views

Best way to store data with small differences for collective analysis

I have a collection of CSV files each containing data about the same topic, but with small differences. For example units carrying out a job might in one file be identified by one field, in the other ...
1
vote
0answers
17k views

how to import .xls file into SQLite database?

I have an issue with importing .xls file into SQLite database. I am working on windows xp and using a SQLite manager for maintain the database. I can import the .csv file to the database by using ...
1
vote
2answers
8k 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
0answers
21 views

PostgreSQL 10 - create partitions on column that needs to be calculated after importing a csv

I am in the situation where I need to create a table which also has a time column. This table is filled with data taken from a CSV, but the csv has no time column. Only after importing the data inside ...
0
votes
1answer
24 views

attach csv file on email sent from postgres

I have the following code to send emails and wanted to see if it's possible to attach a csv file? CREATE OR REPLACE FUNCTION public.send_email_html(text, text, text, text) RETURNS integer LANGUAGE ...
0
votes
0answers
122 views

Persistent problems importing CSV files via pgAdmin 4 to PostgreSQL 12 on Mac OS - Failing with Exit Code -6

I've been trying for several days to import CSV files into a PostgreSQL 12 database hosted on Mac OS 10.15.5, using the Import/Export tool in pgAdmin 4. I am consistently getting an error message ...
0
votes
0answers
29 views

Attempted to import .csv into mySQL using workbench, but only imports 7 rows?

To start, I'm still a relative SQL noob so this question could have an obvious answer that I'm just missing. I'm trying to start a big practice project in my spare time using a dataset which has ~...
0
votes
1answer
28 views

How can I monitor the import progress of a large CSV File in mysql?

I am trying to import an existing CSV file into my database and I want to see the progress. I usually use pipe viewer when I do my sql files but for csv files I cannot figure out how to do it. I use ...
0
votes
0answers
24 views

CommandLine for Exporting All MS SQL Tables to CSV

On my Mac, I have a docker container running an Ubuntu version of MS SQL. I have a DB running in it and I can access it via Azure Data Studio, and I can view each table individually and export it to ...
0
votes
1answer
51 views

How to Export MS SQL DB from Docker-Linux to Access format

I have a MS SQL database which is currently up and running on my Mac within a Docker container that is hosting the Ubuntu Linux version of MS SQL Server. I need to export the database to a format ...
0
votes
1answer
54 views

Mysql CSV import when using LOAD DATA LOCAL INFILE only imports half of the rows

I am doing an import with below code and it works but only imports every second row (1,3,5,7...) and only imports half the total csv contents at the end. LOAD DATA LOCAL INFILE "'.$file.'" INTO TABLE ...
0
votes
0answers
25 views

Mongo compass import csv with accentuation

I'm trying to solve the following problem ... When trying to import data from a csv file into my collection, it happens that in the database some words with accents are left with question marks. ...
0
votes
0answers
213 views

Import from csv file to SQL table - Double quotes on the very end

I was able to import a .CSV file into my sql table, here is my BULK INSERT script: BULK INSERT TLS_Sysprocess FROM 'D:\Dennis\DevAPPServers2.csv' WITH ( FirstRow=2 ,FIELDTERMINATOR = '","' ,...
0
votes
1answer
66 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
1answer
41 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 ...
0
votes
0answers
8 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
34 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 ...
0
votes
0answers
37 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
97 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 ...
0
votes
1answer
29 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'...
0
votes
0answers
68 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
168 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
21 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)...
0
votes
2answers
2k 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]}, ...
0
votes
1answer
39 views

csv engine and mysql start

my question is about speed when perform service mysql start. with many tables (some not often used). what engines consumes more or less start time ? example: innodb --> the more tables, the more ...
0
votes
0answers
328 views

MySQL automatic import of (huge) csv files fails using mysqlimport

For testing purposes, I have to insert large Time Series data on a MySQL database, running on a Windows remote server. Firstly, the (very basic) table: CREATE TABLE 'sensor_data' ( '...
0
votes
0answers
43 views

mysql: make the server fail instead of just giving warnings

I have a mysql 5.7 server. I am trying to import some .csv files. Sometimes it may so happen that these are malformed. Here is a sample case: - 'mysql: [Warning] Using a password on the command ...
0
votes
1answer
158 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
1answer
669 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 ...