sqlSave包含帶有換行符的單元格的表到Access

我有將多個資料框匯出到 Access 資料庫中的各個作業表的代碼。每個作業表中的一列包含包含多行字串的單元格（即，使用通過 R 的“\r\n”參考插入到字串中的中斷）。

我遇到的問題是 sqlSave 似乎想要截斷字串，以便訪問中的匯出單元格僅包含第一行多行值。換行符對我的目的至關重要。想知道如何/是否可以將包含多行字串的列從 R 傳遞到 Access 中。

編輯

代碼示例：

# establish output dB connection
db_Out <- odbcConnectAccess2007("./Out_Database.accdb")
            
# write in table  
sqlSave(db_Out,FVS_StandInit,tablename="FVS_StandInit",colnames=FALSE,rownames=FALSE)

資料樣本：

    "","Stand_ID","Variant","Inv_Year","Groups","AddFiles","FVSKeywords","Latitude","Longitude","Region","Forest","District","Compartment","Location","Ecoregion","PV_Code","PV_Ref_Code","Age","Aspect","Slope","Elevation","ElevFt","Basal_Area_Factor","Inv_Plot_Size","Brk_DBH","Num_Plots","NonStk_Plots","Sam_Wt","Stk_Pcnt","DG_Trans","DG_Measure","HTG_Trans","HTG_Measure","Mort_Measure","Max_BA","Max_SDI","Site_Species","Model_Type","Physio_Region","Forest_Type","State","County","Fuel_Model","Fuel_0_25_H","Fuel_25_1_H","Fuel_1_3_H","Fuel_3_6_H","Fuel_6_12_H","Fuel_12_20_H","Fuel_20_35_H","Fuel_35_50_H","Fuel_gt_50_H","Fuel_0_25_S","Fuel_25_1_S","Fuel_1_3_S","Fuel_3_6_S","Fuel_6_12_S","Fuel_12_20_S","Fuel_20_35_S","Fuel_35_50_S","Fuel_gt_50_S","Fuel_Litter","Fuel_Duff","Photo_Ref","Photo_code","Site_Index"
"3991","MRCP26D5P194017","ne",NA,"All_Stands Model=RCP26 District=5 Stand_Treatment=NA Species_Group=dI Crown_Density=NA Site_Quality=M",NA,"NOCALIB !... ....1.... ....2.... ....3.... ....4  BAIMULT         2020         ABBA         0.935186422557798 BAIMULT         2020         PIMA         0.975222786707754 BAIMULT         2020         POTR5         0.992727272727273 BAIMULT         2020         LALA         0.867079155109878 BAIMULT         2020         BEPA         0.880310880829016 BAIMULT         2020         PIST         1.0259067357513 BAIMULT         2020         PIGL         NA BAIMULT         2030         ABBA         0.910264686894771 BAIMULT         2030         PIMA         0.950692205575574 BAIMULT         2030         POTR5         0.870588235294118 BAIMULT         2030         LALA         0.482878593664164 BAIMULT         2030         BEPA         0.679946284691137 BAIMULT         2030         PIST         1.18089990817264 BAIMULT         2030         PIGL         NaN BAIMULT         2040         ABBA         0.956073595992679 BAIMULT         2040         PIMA         0.935193843055455 BAIMULT         2040         POTR5         0.463350785340314 BAIMULT         2040         LALA         0.398240703718513 BAIMULT         2040         BEPA         1.04770531400966 BAIMULT         2040         PIST         NA BAIMULT         2040         PIGL         NaN BAIMULT         2050         ABBA         0.902944002795492 BAIMULT         2050         PIMA         0.943314552700289 BAIMULT         2050         POTR5         0.46 BAIMULT         2050         LALA         0.148836259697836 BAIMULT         2050         BEPA         0.93010752688172 BAIMULT         2050         PIST         NA BAIMULT         2050         PIGL         NaN BAIMULT         2060         ABBA         0.985267034990792 BAIMULT         2060         PIMA         0.935951441175495 BAIMULT         2060         POTR5         0.201219512195122 BAIMULT         2060         LALA         0.161596548004315 BAIMULT         2060         BEPA         1.26181818181818 BAIMULT         2060         PIST         NA BAIMULT         2060         PIGL         NaN BAIMULT         2070         ABBA         1.01744488804027 BAIMULT         2070         PIMA         0.947505162880554 BAIMULT         2070         POTR5         NA BAIMULT         2070         LALA         0.235014836795252 BAIMULT         2070         BEPA         1.13799621928166 BAIMULT         2070         PIST         NA BAIMULT         2070         PIGL         NA BAIMULT         2080         ABBA         1.06913254073096 BAIMULT         2080         PIMA         0.962664689827139 BAIMULT         2080         POTR5         NA BAIMULT         2080         LALA         0.233418367346939 BAIMULT         2080         BEPA         1.12131837307153 BAIMULT         2080         PIST         NA BAIMULT         2080         PIGL         NaN BAIMULT         2090         ABBA         0.902231578947368 BAIMULT         2090         PIMA         1.02633506702806 BAIMULT         2090         POTR5         NA BAIMULT         2090         LALA         NA BAIMULT         2090         BEPA         1.3359173126615 BAIMULT         2090         PIST         NA BAIMULT         2090         PIGL         NaN ",49.1908333333333,-54.4540833332804,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,20.5369669273036
"3992","MRCP26D5P194017","ne",NA,"All_Stands Model=RCP26 District=5 Stand_Treatment=NA Species_Group=dI Crown_Density=NA Site_Quality=M",NA,"NOCALIB !... ....1.... ....2.... ....3.... ....4  BAIMULT         2020         ABBA         0.935186422557798 BAIMULT         2020         PIMA         0.975222786707754 BAIMULT         2020         POTR5         0.992727272727273 BAIMULT         2020         LALA         0.867079155109878 BAIMULT         2020         BEPA         0.880310880829016 BAIMULT         2020         PIST         1.0259067357513 BAIMULT         2020         PIGL         NA BAIMULT         2030         ABBA         0.910264686894771 BAIMULT         2030         PIMA         0.950692205575574 BAIMULT         2030         POTR5         0.870588235294118 BAIMULT         2030         LALA         0.482878593664164 BAIMULT         2030         BEPA         0.679946284691137 BAIMULT         2030         PIST         1.18089990817264 BAIMULT         2030         PIGL         NaN BAIMULT         2040         ABBA         0.956073595992679 BAIMULT         2040         PIMA         0.935193843055455 BAIMULT         2040         POTR5         0.463350785340314 BAIMULT         2040         LALA         0.398240703718513 BAIMULT         2040         BEPA         1.04770531400966 BAIMULT         2040         PIST         NA BAIMULT         2040         PIGL         NaN BAIMULT         2050         ABBA         0.902944002795492 BAIMULT         2050         PIMA         0.943314552700289 BAIMULT         2050         POTR5         0.46 BAIMULT         2050         LALA         0.148836259697836 BAIMULT         2050         BEPA         0.93010752688172 BAIMULT         2050         PIST         NA BAIMULT         2050         PIGL         NaN BAIMULT         2060         ABBA         0.985267034990792 BAIMULT         2060         PIMA         0.935951441175495 BAIMULT         2060         POTR5         0.201219512195122 BAIMULT         2060         LALA         0.161596548004315 BAIMULT         2060         BEPA         1.26181818181818 BAIMULT         2060         PIST         NA BAIMULT         2060         PIGL         NaN BAIMULT         2070         ABBA         1.01744488804027 BAIMULT         2070         PIMA         0.947505162880554 BAIMULT         2070         POTR5         NA BAIMULT         2070         LALA         0.235014836795252 BAIMULT         2070         BEPA         1.13799621928166 BAIMULT         2070         PIST         NA BAIMULT         2070         PIGL         NA BAIMULT         2080         ABBA         1.06913254073096 BAIMULT         2080         PIMA         0.962664689827139 BAIMULT         2080         POTR5         NA BAIMULT         2080         LALA         0.233418367346939 BAIMULT         2080         BEPA         1.12131837307153 BAIMULT         2080         PIST         NA BAIMULT         2080         PIGL         NaN BAIMULT         2090         ABBA         0.902231578947368 BAIMULT         2090         PIMA         1.02633506702806 BAIMULT         2090         POTR5         NA BAIMULT         2090         LALA         NA BAIMULT         2090         BEPA         1.3359173126615 BAIMULT         2090         PIST         NA BAIMULT         2090         PIGL         NaN ",49.1908333333333,-54.4540833332804,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,20.5369669273036
"3993","MRCP26D5P194017","ne",NA,"All_Stands Model=RCP26 District=5 Stand_Treatment=NA Species_Group=dI Crown_Density=NA Site_Quality=M",NA,"NOCALIB !... ....1.... ....2.... ....3.... ....4  BAIMULT         2020         ABBA         0.935186422557798 BAIMULT         2020         PIMA         0.975222786707754 BAIMULT         2020         POTR5         0.992727272727273 BAIMULT         2020         LALA         0.867079155109878 BAIMULT         2020         BEPA         0.880310880829016 BAIMULT         2020         PIST         1.0259067357513 BAIMULT         2020         PIGL         NA BAIMULT         2030         ABBA         0.910264686894771 BAIMULT         2030         PIMA         0.950692205575574 BAIMULT         2030         POTR5         0.870588235294118 BAIMULT         2030         LALA         0.482878593664164 BAIMULT         2030         BEPA         0.679946284691137 BAIMULT         2030         PIST         1.18089990817264 BAIMULT         2030         PIGL         NaN BAIMULT         2040         ABBA         0.956073595992679 BAIMULT         2040         PIMA         0.935193843055455 BAIMULT         2040         POTR5         0.463350785340314 BAIMULT         2040         LALA         0.398240703718513 BAIMULT         2040         BEPA         1.04770531400966 BAIMULT         2040         PIST         NA BAIMULT         2040         PIGL         NaN BAIMULT         2050         ABBA         0.902944002795492 BAIMULT         2050         PIMA         0.943314552700289 BAIMULT         2050         POTR5         0.46 BAIMULT         2050         LALA         0.148836259697836 BAIMULT         2050         BEPA         0.93010752688172 BAIMULT         2050         PIST         NA BAIMULT         2050         PIGL         NaN BAIMULT         2060         ABBA         0.985267034990792 BAIMULT         2060         PIMA         0.935951441175495 BAIMULT         2060         POTR5         0.201219512195122 BAIMULT         2060         LALA         0.161596548004315 BAIMULT         2060         BEPA         1.26181818181818 BAIMULT         2060         PIST         NA BAIMULT         2060         PIGL         NaN BAIMULT         2070         ABBA         1.01744488804027 BAIMULT         2070         PIMA         0.947505162880554 BAIMULT         2070         POTR5         NA BAIMULT         2070         LALA         0.235014836795252 BAIMULT         2070         BEPA         1.13799621928166 BAIMULT         2070         PIST         NA BAIMULT         2070         PIGL         NA BAIMULT         2080         ABBA         1.06913254073096 BAIMULT         2080         PIMA         0.962664689827139 BAIMULT         2080         POTR5         NA BAIMULT         2080         LALA         0.233418367346939 BAIMULT         2080         BEPA         1.12131837307153 BAIMULT         2080         PIST         NA BAIMULT         2080         PIGL         NaN BAIMULT         2090         ABBA         0.902231578947368 BAIMULT         2090         PIMA         1.02633506702806 BAIMULT         2090         POTR5         NA BAIMULT         2090         LALA         NA BAIMULT         2090         BEPA         1.3359173126615 BAIMULT         2090         PIST         NA BAIMULT         2090         PIGL         NaN ",49.1908333333333,-54.4540833332804,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,20.5369669273036
"3994","MRCP26D5P194018","ne",NA,"All_Stands Model=RCP26 District=5 Stand_Treatment=NA Species_Group=bS Crown_Density=2 Site_Quality=M",NA,"NOCALIB !... ....1.... ....2.... ....3.... ....4  BAIMULT         2020         ABBA         0.935186422557798 BAIMULT         2020         PIMA         0.975222786707754 BAIMULT         2020         POTR5         0.992727272727273 BAIMULT         2020         LALA         0.867079155109878 BAIMULT         2020         BEPA         0.880310880829016 BAIMULT         2020         PIST         1.0259067357513 BAIMULT         2020         PIGL         NA BAIMULT         2030         ABBA         0.910264686894771 BAIMULT         2030         PIMA         0.950692205575574 BAIMULT         2030         POTR5         0.870588235294118 BAIMULT         2030         LALA         0.482878593664164 BAIMULT         2030         BEPA         0.679946284691137 BAIMULT         2030         PIST         1.18089990817264 BAIMULT         2030         PIGL         NaN BAIMULT         2040         ABBA         0.956073595992679 BAIMULT         2040         PIMA         0.935193843055455 BAIMULT         2040         POTR5         0.463350785340314 BAIMULT         2040         LALA         0.398240703718513 BAIMULT         2040         BEPA         1.04770531400966 BAIMULT         2040         PIST         NA BAIMULT         2040         PIGL         NaN BAIMULT         2050         ABBA         0.902944002795492 BAIMULT         2050         PIMA         0.943314552700289 BAIMULT         2050         POTR5         0.46 BAIMULT         2050         LALA         0.148836259697836 BAIMULT         2050         BEPA         0.93010752688172 BAIMULT         2050         PIST         NA BAIMULT         2050         PIGL         NaN BAIMULT         2060         ABBA         0.985267034990792 BAIMULT         2060         PIMA         0.935951441175495 BAIMULT         2060         POTR5         0.201219512195122 BAIMULT         2060         LALA         0.161596548004315 BAIMULT         2060         BEPA         1.26181818181818 BAIMULT         2060         PIST         NA BAIMULT         2060         PIGL         NaN BAIMULT         2070         ABBA         1.01744488804027 BAIMULT         2070         PIMA         0.947505162880554 BAIMULT         2070         POTR5         NA BAIMULT         2070         LALA         0.235014836795252 BAIMULT         2070         BEPA         1.13799621928166 BAIMULT         2070         PIST         NA BAIMULT         2070         PIGL         NA BAIMULT         2080         ABBA         1.06913254073096 BAIMULT         2080         PIMA         0.962664689827139 BAIMULT         2080         POTR5         NA BAIMULT         2080         LALA         0.233418367346939 BAIMULT         2080         BEPA         1.12131837307153 BAIMULT         2080         PIST         NA BAIMULT         2080         PIGL         NaN BAIMULT         2090         ABBA         0.902231578947368 BAIMULT         2090         PIMA         1.02633506702806 BAIMULT         2090         POTR5         NA BAIMULT         2090         LALA         NA BAIMULT         2090         BEPA         1.3359173126615 BAIMULT         2090         PIST         NA BAIMULT         2090         PIGL         NaN ",49.1983611106873,-54.4343611111244,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,31.103679061585
"3995","MRCP26D5P194018","ne",NA,"All_Stands Model=RCP26 District=5 Stand_Treatment=NA Species_Group=bS Crown_Density=2 Site_Quality=M",NA,"NOCALIB !... ....1.... ....2.... ....3.... ....4  BAIMULT         2020         ABBA         0.935186422557798 BAIMULT         2020         PIMA         0.975222786707754 BAIMULT         2020         POTR5         0.992727272727273 BAIMULT         2020         LALA         0.867079155109878 BAIMULT         2020         BEPA         0.880310880829016 BAIMULT         2020         PIST         1.0259067357513 BAIMULT         2020         PIGL         NA BAIMULT         2030         ABBA         0.910264686894771 BAIMULT         2030         PIMA         0.950692205575574 BAIMULT         2030         POTR5         0.870588235294118 BAIMULT         2030         LALA         0.482878593664164 BAIMULT         2030         BEPA         0.679946284691137 BAIMULT         2030         PIST         1.18089990817264 BAIMULT         2030         PIGL         NaN BAIMULT         2040         ABBA         0.956073595992679 BAIMULT         2040         PIMA         0.935193843055455 BAIMULT         2040         POTR5         0.463350785340314 BAIMULT         2040         LALA         0.398240703718513 BAIMULT         2040         BEPA         1.04770531400966 BAIMULT         2040         PIST         NA BAIMULT         2040         PIGL         NaN BAIMULT         2050         ABBA         0.902944002795492 BAIMULT         2050         PIMA         0.943314552700289 BAIMULT         2050         POTR5         0.46 BAIMULT         2050         LALA         0.148836259697836 BAIMULT         2050         BEPA         0.93010752688172 BAIMULT         2050         PIST         NA BAIMULT         2050         PIGL         NaN BAIMULT         2060         ABBA         0.985267034990792 BAIMULT         2060         PIMA         0.935951441175495 BAIMULT         2060         POTR5         0.201219512195122 BAIMULT         2060         LALA         0.161596548004315 BAIMULT         2060         BEPA         1.26181818181818 BAIMULT         2060         PIST         NA BAIMULT         2060         PIGL         NaN BAIMULT         2070         ABBA         1.01744488804027 BAIMULT         2070         PIMA         0.947505162880554 BAIMULT         2070         POTR5         NA BAIMULT         2070         LALA         0.235014836795252 BAIMULT         2070         BEPA         1.13799621928166 BAIMULT         2070         PIST         NA BAIMULT         2070         PIGL         NA BAIMULT         2080         ABBA         1.06913254073096 BAIMULT         2080         PIMA         0.962664689827139 BAIMULT         2080         POTR5         NA BAIMULT         2080         LALA         0.233418367346939 BAIMULT         2080         BEPA         1.12131837307153 BAIMULT         2080         PIST         NA BAIMULT         2080         PIGL         NaN BAIMULT         2090         ABBA         0.902231578947368 BAIMULT         2090         PIMA         1.02633506702806 BAIMULT         2090         POTR5         NA BAIMULT         2090         LALA         NA BAIMULT         2090         BEPA         1.3359173126615 BAIMULT         2090         PIST         NA BAIMULT         2090         PIGL         NaN ",49.1983611106873,-54.4343611111244,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,31.103679061585

人們可能會推斷出換行符在哪里，但為了清楚起見，列 [7]（“FVSKeywords”）通常存盤如下，具體內容因一個單元格而異：

"NOCALIB 
!... ....1.... ....2.... ....3.... ....4  
BAIMULT         2020         ABBA         0.935186422557798 
BAIMULT         2020         PIMA         0.975222786707754 
BAIMULT         2020         POTR5         0.992727272727273 
BAIMULT         2020         LALA         0.867079155109878 
BAIMULT         2020         BEPA         0.880310880829016 
BAIMULT         2020         PIST         1.0259067357513 
BAIMULT         2020         PIGL         NA 
BAIMULT         2030         ABBA         0.910264686894771 
BAIMULT         2030         PIMA         0.950692205575574 
BAIMULT         2030         POTR5         0.870588235294118 
BAIMULT         2030         LALA         0.482878593664164 
BAIMULT         2030         BEPA         0.679946284691137 
BAIMULT         2030         PIST         1.18089990817264 
BAIMULT         2030         PIGL         NaN 
BAIMULT         2040         ABBA         0.956073595992679 
BAIMULT         2040         PIMA         0.935193843055455 
BAIMULT         2040         POTR5         0.463350785340314 
BAIMULT         2040         LALA         0.398240703718513 
BAIMULT         2040         BEPA         1.04770531400966 
BAIMULT         2040         PIST         NA 
BAIMULT         2040         PIGL         NaN 
BAIMULT         2050         ABBA         0.902944002795492 
BAIMULT         2050         PIMA         0.943314552700289 
BAIMULT         2050         POTR5         0.46 
BAIMULT         2050         LALA         0.148836259697836 
BAIMULT         2050         BEPA         0.93010752688172 
BAIMULT         2050         PIST         NA 
BAIMULT         2050         PIGL         NaN 
BAIMULT         2060         ABBA         0.985267034990792 
BAIMULT         2060         PIMA         0.935951441175495 
BAIMULT         2060         POTR5         0.201219512195122 
BAIMULT         2060         LALA         0.161596548004315 
BAIMULT         2060         BEPA         1.26181818181818 
BAIMULT         2060         PIST         NA 
BAIMULT         2060         PIGL         NaN 
BAIMULT         2070         ABBA         1.01744488804027 
BAIMULT         2070         PIMA         0.947505162880554 
BAIMULT         2070         POTR5         NA 
BAIMULT         2070         LALA         0.235014836795252 
BAIMULT         2070         BEPA         1.13799621928166 
BAIMULT         2070         PIST         NA 
BAIMULT         2070         PIGL         NA 
BAIMULT         2080         ABBA         1.06913254073096 
BAIMULT         2080         PIMA         0.962664689827139 
BAIMULT         2080         POTR5         NA 
BAIMULT         2080         LALA         0.233418367346939 
BAIMULT         2080         BEPA         1.12131837307153 
BAIMULT         2080         PIST         NA 
BAIMULT         2080         PIGL         NaN 
BAIMULT         2090         ABBA         0.902231578947368 
BAIMULT         2090         PIMA         1.02633506702806 
BAIMULT         2090         POTR5         NA 
BAIMULT         2090         LALA         NA 
BAIMULT         2090         BEPA         1.3359173126615 
BAIMULT         2090         PIST         NA 
BAIMULT         2090         PIGL         NaN "

uj5u.com熱心網友回復：

根據評論，考慮將基礎列更改為長文本（或備忘錄）以容納超過 255 個字符的短文本限制的所有字符資料。然后，運行資料框遷移。請參閱 Access SQL 的ALTER TABLE陳述句。

sqlQuery(db_Out, "ALTER TABLE FVS_StandInit ALTER COLUMN FVSKeywords LONGTEXT")

sqlSave(db_Out, FVS_StandInit, tablename="FVS_StandInit", colnames=FALSE, rownames=FALSE)

標籤：sql r 细绳 毫秒访问 出口

上一篇：必須搜索子字串是否存在于字串中

下一篇：如何使用pandas搜索一組特定的列