How to Import CSV Data into a Table Without Knowing the Columns of the Csv

Can I import CSV data into a table without knowing the columns of the CSV?

The error is normal.
You created a table with no column. The COPY command try to import data into the table with the good structure.
So you have to create the table corresponding to your csv file before execute the COPY command.

I discovered pgfutter :

"Import CSV and JSON into PostgreSQL the easy way. This small tool abstract all the hassles and swearing you normally have to deal with when you just want to dump some data into the database"

Perhaps a solution ...

Import specific values from csv files to column of existing table

As @Alex pointed out, the best way to solve this is in a few steps.

Step 1: Getting your data into a #temp table.

Step 2: Do a look up from the Employee table into your loaded data.

Step 3: Update the Employee table with your found new found information!

NOTE:
Depending on the size of your data you may want to add a index to your temp tables. In addition joining on name has the chance for row expansion that you may need to contend with.

-- Step 1:
drop table if exists #tmp_Employee_Data;
BULK INSERT #tmp_Employee_Data
FROM 'D:\Employees.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
)

-- Step 2: Using the temp table you can do your additional work.
drop table if exists #tmp_EmpSup;
select t.*, e.EmployeeId as SuperviserId
into #tmp_EmpSup
from Employee e join #tmp_Employee_Data t
on e.FirstName + ' ' + e.LastName = t.Supervisor

-- Step 3: Update your Table
update e
set e.SupervisorId = t.SuperviserId
from Employee e join #tmp_EmpSup t
on e.FirstName = t.FirstName
and e.LastName = t.LastName

Good Luck!

How to load a csv file without knowing the number of columns beforehand

My understanding is that you want to sum the frequencies (first column) for each disease, and create a bar chart using those frequencies. You can change how you process the data loaded from the CSV file:

d3.csv("diseases.csv", type, function(error, permutations) {
var diseases = d3.keys(permutations[0]).filter(function(key) { return key != "frequency";}),
data = diseases.map(function(d){ return {disease: d, frequency: 0}});
permutations.forEach(function(row){
diseases.forEach(function(d, i){
if (row[d] === 1){
data[i].frequency += row["frequency"];
}
})
})

to store your data in an array that looks like this:

[{"disease":"disease1","frequency":81},{"disease":"disease2","frequency":30},
{"disease":"disease3","frequency":61},{"disease":"disease4","frequency":62}]

Then just modify the x domain:

x.domain(diseases);

and the x attribute when you draw your <rect>s to use the particular disease:

    .attr("x", function(d) { return x(d.disease); })

Making these changes gives me the following bar chart:

enter image description here

How to import a CSV file into a BigQuery table without any column names or schema?



Related Topics



Leave a reply



Submit