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).
225
questions
0
votes
1answer
19 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
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
1answer
37 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 ...
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
2answers
23 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
36 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
48 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
33 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 ...
0
votes
1answer
58 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 ...
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
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 ...
1
vote
1answer
483 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
0answers
61 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
250 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
613 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=';', ...
0
votes
3answers
639 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
0answers
21 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
42 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
121 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 ...
1
vote
1answer
117 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
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
305 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. ...
0
votes
2answers
912 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
36 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 ...
1
vote
1answer
131 views
\COPY not returning count psql
I am trying to copy CSV data into tables. Also I want to print the count.
The command I'm using is:
echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -...
2
votes
2answers
1k views
Can't import flat file into SqlServer
I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.)
I see two ...
0
votes
0answers
227 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
35 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
2answers
1k 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 ...
0
votes
1answer
134 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
4k views
foreach loop in sql scripts?
In powershell I use foreach to run a function through a list of targets.
I was wondering that if there is any thing similar to foreach in sql, so I can run the same query through multiple DBs I chose ...
2
votes
0answers
47 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 ...
0
votes
3answers
728 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
1answer
115 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
2answers
549 views
SQL Server Agent - Do not show job step details and column headers in output file
I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file
(Right click the job --> properties --> advanced and then adding a file path to the output ...
1
vote
1answer
5k views
How to automate CSV-exports of queries in PL/SQL Developer (Oracle)?
I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server).
I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer.
I need ...
5
votes
2answers
1k views
How to import CSV file in SQL server 2008?
I am trying to import a CSV file in SQL Server 2008. BULK INSERT is a way to go but it is applicable for CSV from SQL Server 2014 onwards.
What would be an alternative way to achieve this goal?
Any ...
-1
votes
1answer
476 views
Copy in SQL with index vs without index
Recently I have received a data dump in csv format that I am trying to import in my PSQL database. The dump consists of 8 csv file each of size 7GB.
I have found that using the Copy command on one ...
2
votes
1answer
148 views
Handling variable fields in a CSV file with JSON?
Im trying to figure out the engineering to get a CSV file into PostgreSQL.
Requirements:
The CSV has X number of predetermined required fields
The CSV file has Y number of dynamic fields
Example:
...
0
votes
4answers
311 views
SSIS deployment failed - working with files
I've developed a simple SSIS package that will open a csv file, select a number of column, add two derived columns and then copy the results to a file.
This works within visual studio (even if it ...
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
426 views
Bulk Insert csv file error [closed]
I am trying to BULK INSERT a csv file to my SQL Server.
Using code I found on this SO question, I ran:
BULK INSERT Temp
FROM 'c:\csv\student.csv '
WITH (
FIELDTERMINATOR = ',',
...
1
vote
1answer
560 views
How to upload multiple csv files to sql server without constrains
I backed up an old SQL Server DB with .CSV files for each table. The old DB is now gone; the .CSV files are all I have.
Now I want to create new DB and upload all those csv file (my old db removed) .
...
2
votes
1answer
107 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
528 views
postgresql - Importing CSV Data with Custom Type
I'm mostly a newcomer to databases and am building a postgres database of student achievement in a series of courses. My database uses a type called "class" that consists of the course, the academic ...
3
votes
2answers
1k views
How to dump to csv valid json with Postgresql?
I am trying to dump some data as json with:
\copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv ...
1
vote
2answers
187 views
How to split a string on every 2nd delimiter?
I have curves consisting of X/Y points (an array?) stored in a string with the same delimiter. There is 1 curve per row. Below is a simple example:
0,0,1,1,2,1.9,2.9,2.8,3.6,3.5
I want to extract ...
