sqlSave tables containing cells with line breaks into Access-CodePudding

I have code that exports several dataframes to individual sheets in an Access Database. One of the columns in each sheet contains cells that hold multi-line strings (i.e., breaks inserted into strings using "\r\n" references through R).

The issue I'm running into is that sqlSave seems to want to truncate the strings so that the exported cells in access contain the first line of multi-line values only. The line breaks are critical for my purposes. Wondering how/if I can pass columns containing multi-line strings into Access from R.

EDIT

Code Sample:

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

Data Sample:

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

One can likely infer where the line breaks are, but for sake of clarity, column [7] ("FVSKeywords") is generally stored as follows, with the specific content varying from one cell to the next:

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

CodePudding user response：

Per comments, consider changing the underlying column to long text (or memo) to accommodate all character data that exceeds the limit of short text at 255 characters. Then, run your data frame migration. See Access SQL's ALTER TABLE statement.

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

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