Import CSV to Postgresql

-1

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?

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

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

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

I am working on a Django based web application.

%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?

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

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

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

I am working on a Django based web application.

%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?

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

I am working on a Django based web application.

%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

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

edited Nov 22 '17 at 9:11

asked Nov 22 '17 at 9:08

Deimos620

96113

asked Nov 22 '17 at 9:08

Deimos620

96113

asked Nov 22 '17 at 9:08

Deimos620

96113

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

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 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

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

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 |

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!')

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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!')

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

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!')

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

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!')

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

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!')

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

1,699258

edited Nov 22 '17 at 15:03

answered Nov 22 '17 at 14:44

Borut

1,699258

answered Nov 22 '17 at 14:44

Borut

1,699258

answered Nov 22 '17 at 14:44

Borut

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

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 |

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Nsryjdtyk