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).
299
questions
1
vote
2
answers
5k
views
postges: psql csv import fails although file exists
Trying to import a csv file as follows:
psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;"
Password for user admin:
ERROR: could ...
1
vote
1
answer
733
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 |
--------------------------------------------------...
1
vote
1
answer
2k
views
Best data upload formats
Most of our automatically generated files contain integers, decimals, date, time, and sometimes varchar. This is instrumentation data. Since we will be uploading those files directly to a SQL Server ...
1
vote
1
answer
1k
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
...
1
vote
1
answer
887
views
how to convert my PostgreSQL database table into CSV? [closed]
i need to convert my PostgreSQL table into csv, how to do this?
i have visited many sites they suggest the PostgreSQL command to convert the table into CSV but i always get the error message.
the ...
1
vote
2
answers
2k
views
Export SSRS report to CSV
I would like to export an SSRS report to a CSV file. Sounds simple, but I do not know how to add double quotes to the column headers in the exported CSV file. For example:
"UserName"|"UserAdd"
"Mike"|...
1
vote
1
answer
51
views
Check value when I have a list of elements separated by commas in a column?
I am looking to return a value in a database.
Table named TIPOLOGY is structured like this:
Name Owner ID Office Type LIST_OF_VALUES
Jones Lasi 1 12345 TKT AUD,PAX,CCF
...
1
vote
3
answers
10k
views
SSIS: How to check if a record DOESN'T exist in flat file but exists on the database
I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. ...
1
vote
1
answer
327
views
Uber-wide tables: Time to switch to Mongo?
As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all ...
1
vote
2
answers
4k
views
import csv file into mysql with custom data change
I have a .csv file contains data like this:
Username Password Email
test123 123 [email protected]
I'm currently using MySQL, and I have a table named user with some columns like this:
...
1
vote
1
answer
653
views
What MySQL data types REQUIRE a max length (size) declaration in a create table statement?
I am converting a SQL Server table structure to a MySQL table structure, and I need to know how to properly declare the column data types within the CREATE TABLE statement.
I have a CSV file where ...
1
vote
1
answer
1k
views
Can I create a MySQL CSV table directly from an existing CSV file?
MySQL has a CSV storage engine.
I'm wondering if I could create such a table directly from an existing CSV file.
Made up command as example:
CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ...
1
vote
1
answer
3k
views
import csv data stored in a blob column
Here's what i need to do:
Users will upload CSV files into SQL Server 2012 blob column
Each night, i would like to take each file and import the data into table
I will have 2 web sites using the same ...
1
vote
1
answer
4k
views
Getting error while importing large data through csv
I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.
I want to disable all of the database constraints temporarily - I do not know how to do this.
1
vote
1
answer
2k
views
Command line export and import CSV in SQL Server 2008 R2
I have a database xyz and 100+ tables and has to perform the following operations on the command line:
I need to create CSV of all tables in one go.
CSV with column separator ,, fields enclosed by " "...
1
vote
2
answers
3k
views
Access exporting to CSV is weirdly slow
I have searched but could not find anything similar. If I just run the query, I can get the result pretty quickly (~5 mins); but after I saved the query to export as CSV, the exporting part is ...
1
vote
1
answer
85
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 ...
1
vote
1
answer
950
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
vote
2
answers
894
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. ...
1
vote
0
answers
4k
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 ...
1
vote
1
answer
856
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 ...
1
vote
0
answers
128
views
How to migrate data from a large amount of csv files with time x space measurements to a database that needs to be available for analysis [closed]
I have made a simulation experiment to obtain time series measurements at different points along a power line. These measurements were done for different types of power lines in different test ...
1
vote
0
answers
41
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
0
answers
671
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 ...
1
vote
0
answers
163
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 ...
1
vote
2
answers
828
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 ...
1
vote
1
answer
1k
views
Oracle XMLAGG issue with combining cells that have comma separated values in cells
Have a situation where I'm trying to use xmlagg to get:
| ID | name
row1 | 1 | apple
row2 | 1 | apple,pear,banana
row3 | 1 | apple,banana
row4 | 1 | pear,banana,orange
row5 | 2 | orange
...
1
vote
1
answer
41
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
0
answers
724
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
0
answers
176
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
1
answer
2k
views
leading 0 missing in column when exporting to csv
I have a query I export which is then used to import products into our website.
One of the ways we help people find products more easily is by using a long and short item code. It's basically the ...
1
vote
0
answers
2k
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
0
answers
99
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
0
answers
456
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
0
answers
142
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
0
answers
698
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
0
answers
435
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
0
answers
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
0
answers
250
views
Import CSV file into SQLServer [duplicate]
Possible Duplicate:
Convert varchar column to datetime
Hi I am using the SQL server Import wizard to import a CSV file into what will be a new table in my database.
An example of the CSV file is:...
1
vote
2
answers
3k
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 > ...
0
votes
2
answers
6k
views
Doing a comparison on two csv files with no Primary Key
I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique.
To give you a better picture from what I mean; In initial load ...
0
votes
3
answers
3k
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 ...
0
votes
3
answers
805
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, ...
0
votes
1
answer
207
views
How to store data having a fixed number of columns and a variable number of rows
I have posted this question in the stack overflow site and a more experienced user than me suggested I post it here.
I am quite new at collecting data and am collecting a new dataset using R from ...
0
votes
2
answers
3k
views
How to retrieve 'y' and 'n' comma separated values as 'yes' or 'no' from a single column
I have a table in a database with these columns
id name facility_Type
1 Taluka y,n,n,y,n
Now I want to retrieve the comma separated values like this
id name Ambulance ward ...
0
votes
3
answers
59
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 ...
0
votes
1
answer
4k
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 ...
0
votes
2
answers
96
views
Using Structured Filetypes as Databases
In having a debate with a coworker it was posed that a flat file, in this case a CSV, could be considered a database. This would include XML and JSON as well. I'm interested in differing views on this....
0
votes
1
answer
147
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 ...
0
votes
2
answers
383
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 ...