Import CSV to Postgresql
I am working on a Django based web application.
I am going to import a csv to postgresql database, which has over 100,000 lines, and use it as a database for the Django application.
Here, I've faced two problems.
The field name includes special characters like this:
%oil, %gas, up/down, CAPEX/Cash-flow, D&C Cape,...
1st, How should I define the field name of Postgresql database to import csv?
2nd, After import, I am going to get data through django model. Then how can I define the Django model variable name that includes special characters?
Of course, It's possible if I change the column name of the csv which includes special characters, but I don't want to change it. I want to import original csv without any changes.
Is there any solution to solve this problem?
django postgresql csv
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
add a comment |
I am working on a Django based web application.
I am going to import a csv to postgresql database, which has over 100,000 lines, and use it as a database for the Django application.
Here, I've faced two problems.
The field name includes special characters like this:
%oil, %gas, up/down, CAPEX/Cash-flow, D&C Cape,...
1st, How should I define the field name of Postgresql database to import csv?
2nd, After import, I am going to get data through django model. Then how can I define the Django model variable name that includes special characters?
Of course, It's possible if I change the column name of the csv which includes special characters, but I don't want to change it. I want to import original csv without any changes.
Is there any solution to solve this problem?
django postgresql csv
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
If you usecopythen you don't even need to specify column names, provided the content lines up identically.
– Hambone
Nov 22 '17 at 12:01
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46
add a comment |
I am working on a Django based web application.
I am going to import a csv to postgresql database, which has over 100,000 lines, and use it as a database for the Django application.
Here, I've faced two problems.
The field name includes special characters like this:
%oil, %gas, up/down, CAPEX/Cash-flow, D&C Cape,...
1st, How should I define the field name of Postgresql database to import csv?
2nd, After import, I am going to get data through django model. Then how can I define the Django model variable name that includes special characters?
Of course, It's possible if I change the column name of the csv which includes special characters, but I don't want to change it. I want to import original csv without any changes.
Is there any solution to solve this problem?
django postgresql csv
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
I am working on a Django based web application.
I am going to import a csv to postgresql database, which has over 100,000 lines, and use it as a database for the Django application.
Here, I've faced two problems.
The field name includes special characters like this:
%oil, %gas, up/down, CAPEX/Cash-flow, D&C Cape,...
1st, How should I define the field name of Postgresql database to import csv?
2nd, After import, I am going to get data through django model. Then how can I define the Django model variable name that includes special characters?
Of course, It's possible if I change the column name of the csv which includes special characters, but I don't want to change it. I want to import original csv without any changes.
Is there any solution to solve this problem?
django postgresql csv
django postgresql csv
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
edited Nov 22 '17 at 9:11
Deimos620
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
asked Nov 22 '17 at 9:08
Deimos620Deimos620
96113
96113
If you usecopythen you don't even need to specify column names, provided the content lines up identically.
– Hambone
Nov 22 '17 at 12:01
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46
add a comment |
If you usecopythen you don't even need to specify column names, provided the content lines up identically.
– Hambone
Nov 22 '17 at 12:01
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46
If you use
copy then you don't even need to specify column names, provided the content lines up identically.– Hambone
Nov 22 '17 at 12:01
If you use
copy then you don't even need to specify column names, provided the content lines up identically.– Hambone
Nov 22 '17 at 12:01
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46
add a comment |
1 Answer
1
active
oldest
votes
There are no special characters in your example. At least not any that would be problematic from the python or database point of view.
First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like
class FinancialPeformanceData(models.Model):
oil_share = models.DecimalField(max_digits=5, decimal_places=2)
gas_share = models.DecimalField(max_digits=5, decimal_places=2)
growth = models.DecimalField(max_digits=10, decimal_places=2)
capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
... etc.
Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.
def import_csv(request):
file = './path/to/file'
with open(file, 'rb') as csvfile:
with closing(connections['database_name_from_settings'].cursor()) as cursor:
cursor.copy_from(
file=csvfile,
table='yourapp_financialperformancedata', #<-- table name from db
sep='|', #<-- delimiter
columns=(
'oil_share',
'gas_share',
'growth',
'capex_to_cf',
... etc.
),
)
return HttpResponse('Done!')
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f47430630%2fimport-csv-to-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are no special characters in your example. At least not any that would be problematic from the python or database point of view.
First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like
class FinancialPeformanceData(models.Model):
oil_share = models.DecimalField(max_digits=5, decimal_places=2)
gas_share = models.DecimalField(max_digits=5, decimal_places=2)
growth = models.DecimalField(max_digits=10, decimal_places=2)
capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
... etc.
Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.
def import_csv(request):
file = './path/to/file'
with open(file, 'rb') as csvfile:
with closing(connections['database_name_from_settings'].cursor()) as cursor:
cursor.copy_from(
file=csvfile,
table='yourapp_financialperformancedata', #<-- table name from db
sep='|', #<-- delimiter
columns=(
'oil_share',
'gas_share',
'growth',
'capex_to_cf',
... etc.
),
)
return HttpResponse('Done!')
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
add a comment |
There are no special characters in your example. At least not any that would be problematic from the python or database point of view.
First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like
class FinancialPeformanceData(models.Model):
oil_share = models.DecimalField(max_digits=5, decimal_places=2)
gas_share = models.DecimalField(max_digits=5, decimal_places=2)
growth = models.DecimalField(max_digits=10, decimal_places=2)
capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
... etc.
Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.
def import_csv(request):
file = './path/to/file'
with open(file, 'rb') as csvfile:
with closing(connections['database_name_from_settings'].cursor()) as cursor:
cursor.copy_from(
file=csvfile,
table='yourapp_financialperformancedata', #<-- table name from db
sep='|', #<-- delimiter
columns=(
'oil_share',
'gas_share',
'growth',
'capex_to_cf',
... etc.
),
)
return HttpResponse('Done!')
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
add a comment |
There are no special characters in your example. At least not any that would be problematic from the python or database point of view.
First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like
class FinancialPeformanceData(models.Model):
oil_share = models.DecimalField(max_digits=5, decimal_places=2)
gas_share = models.DecimalField(max_digits=5, decimal_places=2)
growth = models.DecimalField(max_digits=10, decimal_places=2)
capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
... etc.
Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.
def import_csv(request):
file = './path/to/file'
with open(file, 'rb') as csvfile:
with closing(connections['database_name_from_settings'].cursor()) as cursor:
cursor.copy_from(
file=csvfile,
table='yourapp_financialperformancedata', #<-- table name from db
sep='|', #<-- delimiter
columns=(
'oil_share',
'gas_share',
'growth',
'capex_to_cf',
... etc.
),
)
return HttpResponse('Done!')
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
There are no special characters in your example. At least not any that would be problematic from the python or database point of view.
First of, avoid dubious field names, especially in finance. %oil can mean either oil share, oil margin or something else. Define a model with meaningful names like
class FinancialPeformanceData(models.Model):
oil_share = models.DecimalField(max_digits=5, decimal_places=2)
gas_share = models.DecimalField(max_digits=5, decimal_places=2)
growth = models.DecimalField(max_digits=10, decimal_places=2)
capex_to_cf = models.DecimalField(max_digits=7, decimal_places=2)
... etc.
Then you use copy to import data from CSV as @Hambone suggested. You don't need headers in CSV files.
def import_csv(request):
file = './path/to/file'
with open(file, 'rb') as csvfile:
with closing(connections['database_name_from_settings'].cursor()) as cursor:
cursor.copy_from(
file=csvfile,
table='yourapp_financialperformancedata', #<-- table name from db
sep='|', #<-- delimiter
columns=(
'oil_share',
'gas_share',
'growth',
'capex_to_cf',
... etc.
),
)
return HttpResponse('Done!')
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
edited Nov 22 '17 at 15:03
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
answered Nov 22 '17 at 14:44
BorutBorut
1,699258
1,699258
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
add a comment |
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
Great! Thanks. This helped me out.
– Deimos620
Nov 22 '17 at 14:48
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
You probably won't get it working the first time, but stick with it. It can be done. And my example won't work if you have headers in CSV. I'm importing CSVs with millions of rows this way.
– Borut
Nov 22 '17 at 14:50
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
Thanks. At first i thought header is parsed, too. That's why I think parsing is problematic and declined @hambone's one.
– Deimos620
Nov 22 '17 at 14:53
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f47430630%2fimport-csv-to-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you use
copythen you don't even need to specify column names, provided the content lines up identically.– Hambone
Nov 22 '17 at 12:01
Thanks.@Hambone. But I don't think it's correct soluction.
– Deimos620
Nov 22 '17 at 13:52
You should at least try @Hambone's suggestion, before you disqualify it. His suggestion is good.
– Borut
Nov 22 '17 at 14:46