[SQL] PostgreSQL를 9.3로 가져 오기 엑셀 데이터

PostgreSQL를 9.3로 가져 오기 엑셀 데이터

나는 PostgreSQL 데이터베이스로 전송의 Excel에서 큰 테이블과 현재 직면하고 문제를 개발했습니다. 나는 ODBC 소프트웨어를 다운로드 한 내가 엑셀과 PostgreSQL을에서 만든 테이블을 열 수 있어요. 그러나, 나는 Excel에서 테이블을 생성하는 역 방식으로 그것을 할 수 아니에요과 PostgreSQL의에서 엽니 다. 나는 그것이 원시 꽤 지루하여 데이터 원료를 삽입 원인 pgAdmin III와 큰 테이블을 만들 수있는 다른 방법이이 방법으로 수행이거나 할 수 있습니다 알고 싶습니다 그래서.

(가) 도움이 모든에 미리 감사드립니다!

해결법

1. ==============================

   1.전형적인 대답은 이것이다 :

   전형적인 대답은 이것이다 :

   그러나 너무이 작업을 수행하는 다른 방법이 있습니다. PostgreSQL은 놀라 울 정도로 프로그램 데이터베이스입니다. 이들은 다음을 포함한다 :

   가능성은 말 그대로 끝이 없습니다 ....

2. ==============================

   2.파이썬 위해 당신은 모든 2010 및 최신 파일 형식 (XLSX)에 대한 openpyxl 사용할 수 있습니다.

   파이썬 위해 당신은 모든 2010 및 최신 파일 형식 (XLSX)에 대한 openpyxl 사용할 수 있습니다.

   엑셀로 작업하는 것은 매우 심층적 인 스프레드 시트, 전체 책과 동반 Udemy 물론 훌륭한 자원입니다에 알 Sweigart 자동화 지루한 부분에서 전체 자습서를 가지고있다.

   자신의 예에서

   >>> import openpyxl
   >>> wb = openpyxl.load_workbook('example.xlsx')
   >>> wb.get_sheet_names()
   ['Sheet1', 'Sheet2', 'Sheet3']
   >>> sheet = wb.get_sheet_by_name('Sheet3')
   >>> sheet
   <Worksheet "Sheet3">
   

   이 액세스하면 당연히 당신은 지금 당신이 일반적으로하는 것처럼 포스트 그레스에 데이터를 구문 분석 psycopg 사용할 수 있습니다.

   이 엑셀 VBA 대신에 파이썬을 사용하기위한 기능의 큰 배열을 제공합니다 또한 xlwings 엑셀 - 파이썬에서 파이썬 자원 목록에 대한 링크입니다.

3. ==============================

   3.당신은 또한 \가 PostgreSQL 서버 컴퓨터로 파일을 전송할 필요없이 복사 실행 psql의 콘솔을 사용할 수 있습니다. 이 명령은 동일합니다 :

   당신은 또한 \가 PostgreSQL 서버 컴퓨터로 파일을 전송할 필요없이 복사 실행 psql의 콘솔을 사용할 수 있습니다. 이 명령은 동일합니다 :

   \copy mytable [ ( column_list ) ] FROM '/path/to/csv/file' WITH CSV HEADER
   

4. ==============================

   4.I 사용이 data.frame으로 R로 테이블을로드한다하는 방법은 다음의 PostgreSQL로 밀어 dbWriteTable를 사용한다. 이 두 단계는 다음과 같습니다.

   I 사용이 data.frame으로 R로 테이블을로드한다하는 방법은 다음의 PostgreSQL로 밀어 dbWriteTable를 사용한다. 이 두 단계는 다음과 같습니다.

   R의 data.frame 객체라는 이름의 열은 텍스트 나 숫자로 명시 적 유형을 가질 경우, 데이터베이스 같다. 같은 XLConnect 같은 R로 스프레드 시트를 얻을 수있는 몇 가지 방법이 있습니다. 그러나, 정말 간단한 방법은 (즉, CTRL + C) 복사 (헤더 포함) 엑셀 테이블의 범위를 선택하는 것입니다, 다음 R에 클립 보드에서 그걸 얻기 위해이 명령을 사용합니다 :

   d <- read.table("clipboard", header=TRUE, sep="\t", quote="\"", na.strings="", as.is=TRUE)
   

   당신이 RStudio이있는 경우, 당신은 쉽게 확인이 예상대로 확인하기 위해 D 객체를 볼 수 있습니다.

   당신이 RPostgreSQL이 CRAN에서 설치되어 있는지 확인하고 연결을 확인하고 데이터베이스에 data.frame을 보내 :

   library(RPostgreSQL)
   conn <- dbConnect(PostgreSQL(), dbname="mydb")
   
   dbWriteTable(conn, "some_table_name", d)
   

   이제 some_table_name는 데이터베이스에 나타납니다.

   일반적인 청소 단계는 pgAdmin 또는 psql의에서 수행 할 수 있습니다 :

   ALTER TABLE some_table_name RENAME "row.names" TO id;
   ALTER TABLE some_table_name ALTER COLUMN id TYPE integer USING id::integer;
   ALTER TABLE some_table_name ADD PRIMARY KEY (id);
   

5. ==============================

   5.로 여기에 설명 http://www.postgresonline.com/journal/categories/journal/archives/339-OGR-foreign-data-wrapper-on-Windows-first-taste.html

   로 여기에 설명 http://www.postgresonline.com/journal/categories/journal/archives/339-OGR-foreign-data-wrapper-on-Windows-first-taste.html

   ogr_fdw 모듈을, 그 수는 pgSQL의 외국 테이블로 엑셀 시트를 엽니과를 pgsql의 다른 일반 테이블처럼 직접 쿼리합니다. 이 같은 정기적으로 업데이트되는 테이블에서 데이터를 읽는 데 유용합니다

   이렇게하려면 스프레드 시트 테이블 헤더가 깨끗해야, 이러한 문자 등 광폭 문자 또는 새로운 라인을 처리 할 수없는 현재의 ogr_fdw 드라이버, 당신은 아마 인해를 pgsql에서 열을 참조 할 수 없습니다 문제를 인코딩. (주요 이유는 내가이 멋진 확장 기능을 사용할 수 없습니다.)

   Windows 용 ogr_fdw 사전 빌드 바이너리는 여기 http://winnie.postgis.net/download/windows/pg96/buildbot/extras/ 있습니다 빌드 해당하는 다운로드 링크에 버전 번호를 변경합니다. 같은 이름의 하위 폴더를 덮어 쓰려면 pgSQL의 폴더에 파일의 압축을 풉니 다. 다시 시작를 pgsql. 테스트 드라이브하기 전에, 모듈은 실행하여 설치해야합니다 :

   CREATE EXTENSION ogr_fdw;
   

   간단한에서 사용 :

   를 pgsql에서 생성 된 정의 코드를 실행, 외국 테이블을 생성하고 엑셀 파일에 매핑됩니다. 그것은 일반 테이블처럼 조회 할 수 있습니다.

   같은 테이블 구조와 많은 작은 파일이있는 경우에 특히 유용합니다. 그냥 정의의 경로와 이름을 변경하고, 정의가 충분합니다 업데이트합니다.

   이 플러그인은 XLSX 및 XLS 파일을 모두 지원합니다. 문서에 따르면이 가능 쓰기 데이터 스프레드 시트 파일로 다시 있지만 Excel에서 서식 멋진 잃게됩니다 모든 파일이 쓰기에 재 작성합니다.

   엑셀 파일이 큰 경우. 이 작동하지 않습니다. 이는 내가이 확장을 사용하지 않은 또 다른 이유이다. 그것은 한 번에 데이터를로드합니다. 그러나이 확장 또한 ODBC 인터페이스를 지원, 엑셀 파일에 대한 ODBC 소스를 작성하고이 중간 ODBC 소스를 조회 할 수 ogr_fdw 또는 기타를 pgsql의 ODBC 외부 데이터 래퍼 사용하는 창 'ODBC 엑셀 파일 드라이버를 사용할 수 있어야한다. 이것은 상당히 안정적이어야한다.

   단점은 이전 방식처럼 pgSQL의 내에서 쉽게 파일의 위치 나 이름을 변경할 수 있다는 것입니다.

   친절한 알림. 권한 문제는이 FDW 확장에 적용됩니다. 그는 pgSQL의 서비스에로드 있기 때문이다. 를 pgsql은 엑셀 파일에 대한 접근 권한이 있어야합니다.

6. ==============================

   6.나는 PostgreSQL의 삽입 문을 생성하는 엑셀 /은 PowerPivot을 사용했다. 당신이 계속해서 또 다시 그것을 할 필요가있을 때를 제외하고 잔인한 사람처럼 보인다. 데이터가의 PowerPivot 창에 있으면, 나는 '빌드'삽입 문 CONCATENATE 문에 연속 열을 추가 할 수 있습니다. 나는 마지막 최종 열이 평평 피벗 테이블을 만들 수 있습니다. 복사 pgAdmin와 기존 PostgreSQL의 테이블에 문 삽입 결과를 붙여 넣습니다.

   나는 PostgreSQL의 삽입 문을 생성하는 엑셀 /은 PowerPivot을 사용했다. 당신이 계속해서 또 다시 그것을 할 필요가있을 때를 제외하고 잔인한 사람처럼 보인다. 데이터가의 PowerPivot 창에 있으면, 나는 '빌드'삽입 문 CONCATENATE 문에 연속 열을 추가 할 수 있습니다. 나는 마지막 최종 열이 평평 피벗 테이블을 만들 수 있습니다. 복사 pgAdmin와 기존 PostgreSQL의 테이블에 문 삽입 결과를 붙여 넣습니다.

   예를 들어 두 개의 열 테이블 (내 표는 내가 반복해서 같은 엑셀 /의 PowerPivot와 연속적인 내용을 가져올 수있는 30 열이 있습니다.)

   컬럼 1 {A, B, ...} {1, 2 열 2, ...}

   은 PowerPivot에서 나는 다음과 같은 명령을 사용하여 계산 된 열을 추가 :

   계산 열 1 " '(TABLE_NAME 값으로 삽입"을 가지고

   계산 된 열 2 CONCATENATE을 갖는다 ([계산 된 열 1] CONCATENATE ([열 1] '', ''))

   ... 마지막 칼럼에 도착하고 삽입 문을 종료 할 때까지

   계산 된 열 3 CONCATENATE을 갖는다 ([계산 된 열 2] CONCATENATE ([열 2] " ');"

   다음은 PowerPivot에서 나는 평평 피벗 테이블을 추가하고 난 그냥 복사하는 삽입 문을 모두 가지고 pgAgent에 붙여 넣습니다.

   문 삽입 결과 :

   insert into table_name values ('a','1');
   
   insert into table_name values ('b','2');
   
   insert into table_name values ('c','3');
   

   참고 : 전원 피벗 CONCATENATE 문을 잘 알고 있다면, 당신은 단지 2 개 인자 (너트) 처리 할 수있는 것을 알고있다. 더 허용하면 좋을 것입니다.

7. ==============================

   7.당신은 아파치 POI 라이브러리 (https://poi.apache.org/)를 사용하여 자바 코드를 작성하여 엑셀 파일 내용을로드 처리 할 수 ​​있습니다. 이 라이브러리는 엑셀을 포함한 MS 오피스 응용 프로그램 데이터 작업을위한 개발되고있다.

   당신은 아파치 POI 라이브러리 (https://poi.apache.org/)를 사용하여 자바 코드를 작성하여 엑셀 파일 내용을로드 처리 할 수 ​​있습니다. 이 라이브러리는 엑셀을 포함한 MS 오피스 응용 프로그램 데이터 작업을위한 개발되고있다.

   나는 최근에 당신이 포스트 그레스 데이터베이스에 Excel 파일을로드하는 데 도움이 될 기술을 기반으로 응용 프로그램을 만들었습니다. 응용 프로그램은 http://www.abespalov.com/에 따라 사용할 수 있습니다. 응용 프로그램은 Windows 테스트하지만,뿐만 아니라 리눅스에서 작동합니다.

   응용 프로그램이 자동으로 엑셀 파일과 같은 열이 필요한 테이블을 작성하고 내용 테이블을 채 웁니다. 당신은 동시에 여러 파일을 내보낼 수 있습니다. 당신은 CSV 형식으로 파일을 변환 할 수있는 단계를 건너 뛸 수 있습니다. 응용 프로그램은 XLS 및 XLSX 형식을 처리합니다.

   전체 응용 프로그램 단계는 다음과 같습니다

   {

   fileExtension = FilenameUtils.getExtension(inputSheetFile.getName());
       if (fileExtension.equalsIgnoreCase("xlsx")) {
           workbook = createWorkbook(openOPCPackage(inputSheetFile));
       } else {
           workbook =     
           createWorkbook(openNPOIFSFileSystemPackage(inputSheetFile));
       }
   
   sheet = workbook.getSheetAt(0);
   

   }

   {

   Iterator<Row> rowIterator = InitInputFilesImpl.sheet.rowIterator();
   
   //skip a header
   if (rowIterator.hasNext()) {
       rowIterator.next();
   }
   while (rowIterator.hasNext()) {
       Row row = (Row) rowIterator.next();
       // inserting rows
   }  
   

   }

   여기서 당신은 포스트 그레스 (https://github.com/palych-piter/Excel2DB)에 엑셀을 수출을 위해 만든 응용 프로그램에 대한 모든 자바 코드를 찾을 수 있습니다.

8. ==============================

   8.그것은 ogr2ogr 사용 가능하다 :

   그것은 ogr2ogr 사용 가능하다 :

   C:\Program Files\PostgreSQL\12\bin\ogr2ogr.exe -f "PostgreSQL" PG:"host=someip user=someuser dbname=somedb password=somepw" C:/folder/excelfile.xlsx -nln newtablenameinpostgres -oo AUTODETECT_TYPE=YES
   

   (ogr2ogr은 포스트 그레스 설치 또는 내가 PostGIS와 확장자를 가지고있는 경우 포함되지 않음 확인합니다.)

9. ==============================

   9.당신은 DataGrip 쉽게 그렇게 할 수 있습니다.

   당신은 DataGrip 쉽게 그렇게 할 수 있습니다.

   .

from https://stackoverflow.com/questions/20039856/import-excel-data-into-postgresql-9-3 by cc-by-sa and MIT license