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).
276
questions
0
votes
1
answer
33
views
Importing CSV with a json array of strings in one of the column
The columns of the CSV are of the format text (can include empty lines), date and a JSON array of strings (something like ['a', 'b', 'c']. I've been trying to copy that CSV to a PostgreSQL table (...
- 3
0
votes
0
answers
19
views
CSV Resultset for IN clause
I am working on writing a SQL query to get data from 2 tables with inner join and need some help. Here is the scenario. I have 2 tables say A and B.
table A
Name
Value
1
Red
2
Green
3
Blue
4
...
- 1
0
votes
1
answer
37
views
0 rows from the .csv (semi colon) are imported into MySQL Workbench
I am trying to import the .csv below (columns and 3 first records in txt in differenet blockquotes) into MySQL Workbench using the Table Data Import Wizard. I have tried many ways and realized that ...
0
votes
1
answer
17
views
Postgres improvements in conversion functions for bulk load
I regularly import data from httparchive.org. The data is a MySQL CSV export and I use pgloader, which handles the quirks of this export (\N for NULL), etc.
I also need to do some additional ...
- 229
0
votes
1
answer
15
views
SQL in Access to add leading 0 in front of zip codes
for a project I am working with a table of zip-codes that I imported from a *.csv file, exported form Excel. Those zipcodes all have 5 numbers and are formatted as text within my database. Some of ...
- 1
2
votes
1
answer
469
views
Date not being imported from csv in proper format in MySQL
I am new to MySQL, so kindly explain your answers in a simple way!
I have a csv file which has 2 date columns. I want to import the csv into MySQL and I am using the following method:
Create an empty ...
- 121
1
vote
1
answer
42
views
DB strategies to store data in CSV files?
I'm new to the Postgres community and would love to hear your recommendations.
To give a bit more context, each file is associated with a unique zip code and address and contains 8,760 (365 days x 24 ...
- 13
1
vote
1
answer
579
views
unterminated CSV quoted field when to import csv data file into postgresql
Create a table :
CREATE TABLE num (
id serial NOT NULL ,
adsh VARCHAR(20) NOT NULL,
tag VARCHAR(256) NOT NULL,
version VARCHAR(20) NOT NULL,
coreg VARCHAR(256),
ddate date NOT ...
- 126
0
votes
2
answers
33
views
Export colums containing (geo)JSON elements to CSV while keeping the JSON double quotes properly
I want to export using copy 5 columns of a table, 3 of them contain ID, text and timestamp values but 2 of them contain JSON and GeoJSON elements, e.g.:
// Please, note the non-quoted string Value1
/...
- 216
0
votes
0
answers
30
views
How to save a CSV file that adds double quotes to special characters that enables easy importing?
When I was importing a CSV file to MySql Workbench that had special characters like comma, brackets, etc., few rows that contained data containing special characters were getting missed (, )) etc).
...
0
votes
2
answers
173
views
Sqlite3 import csv column ordering
I have a situation where I have CSV files with column names in the first row, which perfectly match the tables in my SQLite3 db, except they are in a different order. By default, with .import table1....
- 101
0
votes
0
answers
47
views
How to move/copy a CSV file saved in /var/lib/mysql-files without root on a shared hosting server
I'm working with a server that I'm helping someone with to extract data from their database. The database server is no a shared hosting plan as is very very slow to run queries. Query that takes 3 ...
- 101
0
votes
1
answer
238
views
How do I import CSV UUID data into Postgres on the command line?
I'm using PostGres 10. I have this table
> \d myapp_currencyprice;
Table "public.myapp_currencyprice"
Column | Type | Modifiers
----------------...
- 585
0
votes
0
answers
47
views
MariaDB veryslow Inserts Query/Import via Dbeaver
I have a MariaDB server that via DBeaver is getting very slows insert times.
Tables with 10.000 lines only is getting imported in 2minutes
The DB Manager is trying to import CSVs, some with only 80mb ...
0
votes
1
answer
40
views
Get data from 2 tables based on comma separated values Join
I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table.
I have one table "Form table"
+------+---...
- 134
0
votes
3
answers
64
views
Failed to access postgres data directory on VM
I recently started learning psql, right now I was trying to import some csv spreadsheets into a PostgresQL database using the COPY command.
As far as I know, after installing psql, we have a new ...
1
vote
1
answer
80
views
What are the benefits of using XML over CSV files in regards to data dumps? [closed]
Prologue
I've noticed that one benefit of using CSV over XML for data dumps is the fact that fewer disk space is used. See minimal working example below.
Let's suppose we have the following dataset. ...
- 111
0
votes
0
answers
106
views
Edited php.ini but still cannot load csv's into MySQL database
Static analysis:
1 errors were found during analysis.
Unrecognized statement type. (near "optionally" at position 146)
SQL query:
-- Load data into FOOD_NAME load data local infile '....
- 101
4
votes
1
answer
3k
views
Parsing DATE while copying csv file into PostgreSQL table
I have a long series of .csv files, which I want to import into a local database. I believe my query is correct, but there are some problems in parsing DATE and TIMESTAMP columns. PostgreSQL reads ...
- 143
1
vote
3
answers
819
views
Tsql help - doing a large update from CSV file to a table in ssms
I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
- 45
0
votes
0
answers
127
views
Azure Database for MySQL - mysqlimport command
So I'm trying to accomplish the goal of performing a mysqlimport on a Azure MySQL 8 database and I get the following error:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER,...
- 1
2
votes
2
answers
756
views
Database Mail has incorrect encoding of CSV attachment
I have a procedure that generates a resultset, which I attach as a CSV file attachment in sp_sendmail. However, the resulting file is encoded UCS-2 LE BOM (according to notepad++). This causes the ...
- 35
0
votes
1
answer
189
views
importing csv file with ragged right format and " text qualifier
I'm importing csv files in ssis that are in a ragged right format, I've been able to create the columns perfectly fine but for some of the data rows it has included a " text qualifier into the ...
-1
votes
1
answer
60
views
Can I use a string as an end of line delimiter when importing a .csv to MySQL?
Very basic question but I'm struggling with it. When importing a .csv file to MySQL, is it possible to use any character or string as an end of line delimiter?
If so is it possible from the admin ...
0
votes
1
answer
178
views
Export mutliple query output into one excel sheet using Redshift and SQL Developer
I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to ...
0
votes
1
answer
662
views
(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres
So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects.
How I ...
- 5
1
vote
2
answers
208
views
BULK INSERT does not fail when file contains commas instead of semicolons when FIRSTROW > 1
I encountered an issue where I received a CSV file that supposed to be delimited by semicolons (;) but was delimited by commas (,).
Bulk insert called by sqlcmd did not fail but did not insert either. ...
- 304
1
vote
3
answers
818
views
Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS
I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the ...
0
votes
0
answers
140
views
mysqlimport and avoiding table lock
I reviewed the question & answer here (mysqlimport locks all tables system wide) but I seem to still be stuck on this.
I am standing up a new master/slave pair of 5.7 servers, and have already ...
- 37
-2
votes
2
answers
59
views
How to find out WHERE a PG error/warning/log happened?
I have PostgreSQL CSV-log its "errors" according to this structure: https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
It doesn't include the file ...
- 1
1
vote
1
answer
1k
views
SQL Server BULK IMPORT Truncation error
I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file:
UID,...
- 163
-1
votes
1
answer
41
views
How do I log the actual script's name in csvlog? [duplicate]
I have PostgreSQL CSV-log its errors according to this structure: https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
Sadly, from what I can tell, it neither ...
- 1
0
votes
3
answers
125
views
Import daily csv with varying available headers
I receive a csv daily from an external company capturing feedback for us.
Unhelpfully the csv headings vary. There could be any combination of headers.
For example:
day1: heading1, heading2, heading3, ...
- 123
0
votes
0
answers
261
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 ...
- 111
0
votes
1
answer
2k
views
ORA-06502: 'character string buffer too small' when writing to a csv
I have some tables that i want to export. I'm using this package (I called it "CSV"): https://www.williamrobertson.net/documents/refcursor-to-csv.shtml
I was able to write to CSV files ...
- 283
0
votes
1
answer
150
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 ...
- 131
2
votes
2
answers
831
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 ...
- 21
1
vote
1
answer
245
views
Copy csv file into different schema [closed]
I need to import data from a csv file which from database a schema a into another database b schema b.
I used the pgAdmin running the export and when I import the data using the same tool pgAdmin and/...
- 21
1
vote
0
answers
567
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 ...
- 21
1
vote
0
answers
3k
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 ...
- 11
0
votes
0
answers
914
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 ~...
- 1
0
votes
1
answer
1k
views
MS SQL Server: Import Flat File with auto-increment ID
So I've downloaded a sample database. It originally didn't have an ID column so I inserted one in through Excel. Anyways, I went through DatabaseName > Tasks > Import Flat File and the usual ...
- 103
1
vote
2
answers
571
views
RDBMS: Proper way to store data - comma separated variables or in different fields or tables?
Fundamental question that I cannot seem to find an answer on. I have a database that stores line items from receipts along with the username and receipt number.
Right now the lineItem column is just ...
- 113
1
vote
1
answer
22k
views
PostgreSQL: date/time field value out of range
Software:
Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)
I am getting errors due to ...
- 153
0
votes
2
answers
3k
views
How to import multiple CSV files to a postgres table using pgadmin or other method?
I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs?
COPY schema.table ...
- 111
1
vote
1
answer
180
views
SQL (Redshift) SELECT COUNT from CSV column
Hi exchange community,
Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 ...
- 21
0
votes
1
answer
256
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 ...
- 1
1
vote
1
answer
2k
views
MySQL Workbench: I need to update values in a table based on values from a csv
Newbie Problem, but I've tried creating a new table and importing the csv to that then updating, but I have been having trouble with that, any guidance would be greatly appreciated.
I have a csv file ...
- 13
0
votes
0
answers
63
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 ...
- 111
1
vote
1
answer
355
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 ...
- 111