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
1 vote
1 answer
43 views

Flatten a JSONB file - What function/query

In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure: { "Manufacturers":[ { "manufacturer":"Tesla", ...
user avatar
  • 21
0 votes
1 answer
41 views

Converting PostgreSQL dump file into CSV files

I have a PostgreSQL database running in a docker container. I used the below command to get a dump file of the Postgres database - "docker exec -t your-db-container pg_dumpall -c -U postgres > ...
user avatar
1 vote
1 answer
29 views

Upgrading from excel to an actual database

Just started my first internship and I could use some guidance on approaching the project given to me. Simply put, all that needs to be done is to make a database and then transfer the data from a ...
user avatar
0 votes
3 answers
27 views

MySql query taking long time while querying 5s resolution 4 years data

I am downloading MySql query. It is taking long time. Long solution I found is, download 2 months data at a time. I don't like to do it almost 25 times same thing. Is there a better way I can query ...
user avatar
0 votes
1 answer
25 views

Syntax Error in Boolean Null values

I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?
user avatar
0 votes
1 answer
267 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 (...
user avatar
  • 3
0 votes
0 answers
24 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 ...
user avatar
  • 1
0 votes
1 answer
256 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 ...
user avatar
0 votes
1 answer
23 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 ...
user avatar
0 votes
1 answer
68 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 ...
user avatar
  • 1
2 votes
1 answer
1k 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 ...
user avatar
  • 121
1 vote
1 answer
112 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 ...
user avatar
  • 13
1 vote
1 answer
1k 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 ...
user avatar
  • 126
0 votes
2 answers
57 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 /...
user avatar
  • 216
0 votes
0 answers
102 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). ...
user avatar
0 votes
2 answers
298 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....
user avatar
  • 101
0 votes
0 answers
63 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 ...
user avatar
1 vote
1 answer
427 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 ----------------...
user avatar
  • 615
0 votes
0 answers
63 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 ...
user avatar
0 votes
1 answer
80 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" +------+---...
user avatar
0 votes
3 answers
131 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 ...
user avatar
3 votes
1 answer
189 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. ...
user avatar
  • 131
4 votes
1 answer
4k 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 ...
user avatar
1 vote
4 answers
1k 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 ...
user avatar
  • 45
0 votes
0 answers
169 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,...
user avatar
2 votes
2 answers
871 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 ...
user avatar
0 votes
1 answer
267 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 ...
user avatar
-1 votes
1 answer
68 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 ...
user avatar
0 votes
1 answer
201 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 ...
user avatar
0 votes
1 answer
832 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 ...
user avatar
  • 5
1 vote
2 answers
267 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. ...
user avatar
  • 304
1 vote
3 answers
1k 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 ...
user avatar
0 votes
0 answers
173 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 ...
user avatar
  • 37
-2 votes
2 answers
91 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 ...
user avatar
  • 1
1 vote
1 answer
2k 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,...
user avatar
  • 163
-1 votes
1 answer
49 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 ...
user avatar
0 votes
3 answers
165 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, ...
user avatar
  • 123
0 votes
0 answers
305 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 ...
user avatar
  • 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 ...
user avatar
0 votes
1 answer
177 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 ...
user avatar
  • 131
2 votes
2 answers
920 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 ...
user avatar
1 vote
1 answer
263 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/...
user avatar
  • 21
1 vote
0 answers
650 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 ...
user avatar
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 ...
user avatar
0 votes
0 answers
996 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 ~...
user avatar
  • 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 ...
user avatar
1 vote
2 answers
767 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 ...
user avatar
  • 113
1 vote
1 answer
25k 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 ...
user avatar
  • 151
0 votes
3 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 ...
user avatar
1 vote
1 answer
195 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 ...
user avatar

1
2 3 4 5 6