Postgre로 Excel 데이터 가져오기SQL 9.3

Postgre로 Excel 데이터 가져오기SQL 9.3

저는 엑셀에서 거대한 테이블을 개발했고 지금 그것을 postgresql 데이터베이스로 옮기는 문제에 직면해 있습니다.저는 odbc 소프트웨어를 다운로드했고, postgresql에서 작성한 테이블을 excel로 열 수 있습니다.하지만 엑셀에서 테이블을 만들고 postgresql에서 여는 역방향으로는 할 수 없습니다.그래서 저는 이런 방법으로 할 수 있는지, 아니면 pgAdmin III로 큰 테이블을 만들 수 있는 다른 방법이 있는지 알고 싶습니다. 왜냐하면 데이터를 raw로 삽입하는 것은 상당히 지루하기 때문입니다.

일반적인 대답은 다음과 같습니다.

1. Excel, File/Save As에서 CSV를 선택하고 현재 시트를 저장합니다.

2. postgres 사용자가 액세스할 수 있는 Pg 서버의 보류 디렉터리로 전송

3. 포스트그레에서.SQL:

   COPY mytable FROM '/path/to/csv/file' WITH CSV HEADER; -- must be superuser
   

하지만 이것을 할 수 있는 다른 방법도 있습니다.포스트그레SQL은 놀라울 정도로 프로그래밍이 가능한 데이터베이스입니다.여기에는 다음이 포함됩니다.

1. 모듈을 pl/javaU, pl/perlU 또는 기타 신뢰할 수 없는 언어로 작성하여 파일에 액세스하고, 파일을 구문 분석하고, 구조를 관리합니다.

2. CSV 및 fdw_file을 사용하여 유사 테이블로 액세스합니다.

3. DBILink 및 DBD 사용::엑셀

4. Excel 파일을 읽을 수 있는 외국 데이터 래퍼를 직접 작성합니다.

가능성은 말 그대로 무궁무진합니다.

python의 경우 모든 2010 및 최신 파일 형식(xlsx)에 대해 openpyxl을 사용할 수 있습니다.

Al Sweegart는 Excel 스프레드시트로 작업하는 지루한 부분을 자동화하는 전체 튜토리얼을 가지고 있으며, 전체 책과 함께 제공되는 Udemy 과정은 훌륭한 자료입니다.

그의 예에서.

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

당연히 이 액세스 권한을 갖게 되면 일반적으로 하는 것처럼 싸이코그를 사용하여 데이터를 포스트그로 구문 분석할 수 있습니다.

이것은 python-excel에 있는 python 리소스 목록에 대한 링크이며 xlwings는 excel에서 vba 대신 python을 사용하기 위한 많은 기능을 제공합니다.

또한 psql 콘솔을 사용하여 Postgresql 서버 시스템에 파일을 보낼 필요 없이 \copy를 실행할 수 있습니다.명령은 동일합니다.

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

제가 사용하는 방법은 테이블을 Rasa로 로드하는 것입니다.data.frame그 다음에 사용dbWriteTable Postgre어것는넣에그▁▁to▁push▁it▁to는넣.SQL. 이 두 단계는 다음과 같습니다.

Excel 데이터를 R로 로드

R'sdata.frame개체는 데이터베이스와 유사하며 이름이 지정된 열에는 텍스트 또는 숫자와 같은 명시적인 유형이 있습니다.XL Connect와 같이 스프레드시트를 R로 가져오는 몇 가지 방법이 있습니다.그러나 정말 간단한 방법은 Excel 테이블의 범위(헤더 포함)를 선택하고 복사(C예: +)한 다음 R에서 이 명령을 사용하여 클립보드에서 가져오는 것입니다.

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

RStudio를 가지고 , 은 RStudio를 볼 수 .d예상대로인지 확인하기 위한 반대.

포스트그레로 밀어넣기SQL

RPostgre가 있는지 확인합니다.CRAN에서 SQL을 설치한 다음 연결을 만들고 다음을 전송합니다.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);

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

ogr_fdw 모듈을 사용하면 excel 시트를 pgsql의 외부 테이블로 열고 pgsql의 다른 일반 테이블처럼 직접 쿼리할 수 있습니다.이 기능은 동일한 정기적으로 업데이트된 테이블에서 데이터를 읽는 데 유용합니다.

이렇게 하려면 스프레드시트의 테이블 헤더가 깨끗해야 합니다. 현재 ogr_fdw 드라이버는 넓은 문자나 새 줄 등을 처리할 수 없습니다. 인코딩 문제로 인해 pgsql 열을 참조하지 못할 수 있습니다.(이 멋진 확장을 사용할 수 없는 주요 이유)

Windows용 ogr_sysw 사전 빌드 바이너리는 여기에 있습니다. http://winnie.postgis.net/download/windows/pg96/buildbot/extras/ 링크의 버전 번호를 변경하여 해당 빌드를 다운로드합니다.파일 topgsql 폴더를 추출하여 동일한 이름의 하위 폴더를 덮어씁니다.pgsql을 다시 시작합니다.테스트 드라이브를 실행하기 전에 다음을 실행하여 모듈을 설치해야 합니다.

CREATE EXTENSION ogr_fdw;

간략한 사용법:

1. ogr_sysw_info.exe를 사용하여 시트 이름 목록에 대한 엑셀 파일을 검색합니다.

   ogr_fdw_info -s "C:/excel.xlsx"
   

2. "ogr_sysw_info.exe -l"을 사용하여 개별 시트를 프로빙하고 테이블 정의 코드를 생성합니다.

   ogr_fdw_info -s "C:/excel.xlsx" -l "sheetname"
   

생성된 정의 코드를 pgsql에서 실행하면 외부 테이블이 생성되어 Excel 파일에 매핑됩니다.일반 테이블처럼 쿼리할 수 있습니다.

이 기능은 동일한 테이블 구조의 작은 파일이 많은 경우에 특히 유용합니다.정의에서 경로와 이름을 변경하기만 하면 정의를 업데이트하면 됩니다.

이 플러그인은 XLSX 및 XLS 파일을 모두 지원합니다.문서에 따르면 스프레드시트 파일에 데이터를 다시 쓸 수도 있지만 엑셀의 모든 고급 포맷이 손실되어 파일이 쓰기 시 다시 생성됩니다.

엑셀 파일이 큰 경우.이것은 작동하지 않을 것입니다.그것이 제가 이 확장을 사용하지 않은 또 다른 이유입니다.한 번에 데이터를 로드합니다.그러나 이 확장자는 ODBC 인터페이스도 지원하므로 Windows의 ODBC Excel 파일 드라이버를 사용하여 Excel 파일에 대한 ODBC 소스를 만들고 ogr_fdw 또는 다른 pgsql의 ODBC 외부 데이터 래퍼를 사용하여 이 중간 ODBC 소스를 쿼리할 수 있습니다.이것은 상당히 안정적일 것입니다.

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

친절하게 상기시켜주는 것.이 fdw 확장이 pgsql 서비스에 로드되었기 때문에 사용 권한 문제가 이 fdw 확장에 적용됩니다.pgsql은 excel 파일에 대한 액세스 권한이 있어야 합니다.

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

(postgres 설치에 ogr2ogr이 포함되어 있는지 아니면 postgis 확장과 함께 제공되었는지는 확실하지 않습니다.)

Excel/PowerPivot을 사용하여 postgre를 만들었습니다.SQLinsert진술.과잉 살상인 것 같아요 반복해서 해야 할 때만 빼고요데이터가 PowerPivot 창에 표시되면 다음과 같은 열을 추가합니다.concatenate'구축'하기 위한 진술insert진술.마지막 열과 마지막 열을 사용하여 평평한 피벗 테이블을 만듭니다.결과를 복사하여 붙여넣기insert나의 기존 postgre에 대한 진술.pgAdmin이 있는 SQL 테이블입니다.

예: 두 개의 열 테이블(내 테이블에는 동일한 Excel/PowerPivot로 연속 컨텐츠를 가져오는 열이 30개 있습니다.)

열 1 {a,b,...} 열 2 {1,2,...}

PowerPivot에서 다음 명령을 사용하여 계산된 열을 추가합니다.

계산된 열 1의 값"insert into table_name values ('"

계산된 열 2의 값CONCATENATE([Calculated Column 1],CONCATENATE([Column1],"','"))

...마지막 열에 도달하여 삽입 문을 종료해야 합니다.

계산된 열 3은 다음과CONCATENATE([Calculated Column 2],CONCATENATE([Column2],"');"

그런 다음 PowerPivot에서 평평한 피벗 테이블을 추가하고 pgAgent에 복사하여 붙여넣는 모든 삽입 문을 가집니다.

결과 삽입문:

insert into table_name values ('a','1');

insert into table_name values ('b','2');

insert into table_name values ('c','3');

참고: power pivot CONCATENATE 문에 익숙한 경우에는 두 개의 인수(너트)만 처리할 수 있습니다.그것이 더 허용된다면 좋을 것입니다.

Apache POI 라이브러리(https://poi.apache.org/) 를 사용하여 Java 코드를 작성하면 Excel 파일 내용 로드를 처리할 수 있습니다.도서관은 엑셀을 포함한 MS 오피스 애플리케이션 데이터를 활용할 수 있도록 개발되었습니다.

저는 최근에 당신이 Postgres 데이터베이스에 엑셀 파일을 로드하는 데 도움이 되는 기술을 기반으로 애플리케이션을 만들었습니다.이 애플리케이션은 http://www.abespalov.com/ 에서 이용할 수 있습니다.응용 프로그램은 윈도우즈용으로만 테스트되지만 Linux용으로도 작동해야 합니다.

응용프로그램은 Excel 파일과 동일한 열로 필요한 테이블을 자동으로 만들고 테이블에 내용을 채웁니다.여러 파일을 병렬로 내보낼 수 있습니다.파일을 CSV 형식으로 변환하는 단계를 건너뛸 수 있습니다.애플리케이션은 xls 및 xlsx 형식을 처리합니다.

전체 적용 단계는 다음과 같습니다.

1. Excel 파일 내용을 로드합니다.파일 확장명에 따른 코드는 다음과 같습니다.

{

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

sheet = workbook.getSheetAt(0);

}

2. Postgres JDBC 연결 설정
3. Postgres 테이블 만들기
4. 시트 위에서 반복하고 표에 행을 삽입합니다.다음은 Java 코드의 일부입니다.

{

Iterator<Row> rowIterator = InitInputFilesImpl.sheet.rowIterator();

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

}

여기에서 Postgres(https://github.com/palych-piter/Excel2DB) 로 Excel을 내보내기 위해 만든 응용 프로그램의 모든 Java 코드를 찾을 수 있습니다.

가장 간단한 대답은 psql 명령어를 사용하는 것입니다. 무료이며 include///입니다.

psql -U postgres -p 5432 -f sql-command-file.sql

저는 최근에 https://sqlizer.io 을 발견했습니다. 그것은 엑셀 파일에서 삽입문을 만들고 MySQL과 Postgre를 지원합니다.SQL. 대용량 파일을 지원하는지는 잘 모르겠습니다.

DataGrip을 사용하면 이를 쉽게 수행할 수 있습니다.

1. 먼저 Excel 파일을 csv 형식으로 저장합니다. excel 파일을 열고 다른 이름으로 저장합니다.
2. 데이터그립으로 이동한 후 csv 파일에 따라 테이블 구조를 만듭니다. 열 이름으로 열 이름을 Excel 열로 만드는 것이 좋습니다.
3. 데이터베이스의 테이블 이름 목록에서 테이블 이름을 마우스 오른쪽 단추로 클릭한 다음 파일에서 데이터 가져오기를 클릭합니다.그런 다음 변환된 csv 파일을 선택합니다.

.

언급URL : https://stackoverflow.com/questions/20039856/import-excel-data-into-postgresql-9-3