As I'm still learning SQL I'm not sure where to specify the ("null": "")
option. I didn't want to ask here since it seems to be a very rudimentary
question but I have yet to figure it out.On Wed, May 11, 2022 at 8:25 AM Michael Carey <[email protected]> wrote: > @Nicholas: Did this solve it, hopefully? > On 5/9/22 7:10 PM, Ali Alsuliman wrote: > > Nicholas, > The parser does not know how to handle (or rather what value to produce > for) empty values when the field is numeric as in your case (int32). That's > why it's complaining. The parser cannot produce 0 for empty values, > for example. > > Try specifying the option: ("null": ""). This option tells the parser to > produce NULL for empty values. > > Thanks > Ali > > On Sat, May 7, 2022 at 10:38 AM Nicholas Santini <[email protected]> wrote: > >> Thank you so much for providing that example code. I am really learning >> to swim in the deep end here as I have no experience with SQL. >> Unfortunately, when I run your snippet I get the following error >> >> WARNING: Code: 1 "HYR0124: Parsing error at >> /Users/nicholassantini/Downloads/test 2.csv line 2 field 3: empty value" >> >> >> On Thu, May 5, 2022 at 7:40 PM Mike Carey <[email protected]> wrote: >> >>> PS - Here is a snippet from my approach to grading (I use SQL++) based >>> on SQL++'ing against CSV data: >>> >>> CREATE EXTERNAL DATASET Grades(GradeRecord) USING localfs >>> >>> (("path"="localhost:///Users/mikejcarey/Desktop/Teaching/CS122a/CS122AStudentData2021.csv"), >>> ("format"="delimited-text"), ("delimiter"=",")); >>> >>> For you I guess this would be: >>> >>> CREATE EXTERNAL DATASET csv_set(csv_type) USING localfs >>> (("path"="127.0.0.1:///Users/nicholassantini/Downloads/test 2.csv"), >>> ("format"="delimited-text"), ("delimiter"=",")); >>> >>> Try that? >>> >>> >>> On 5/5/22 2:11 PM, Nicholas Santini wrote: >>> >>> Hi all, >>> >>> Im using the following code to load a csv file into my database, >>> However, any time there is an empty field it seems to skip the entire row. >>> For example test,1,3 works but test1,1, doesn't create an entry. Does >>> anyone know why it does this or how to fix it? >>> >>> >>> drop dataverse csv if exists; >>> create dataverse csv; >>> use csv; >>> >>> create type csv_type as { >>> Cross_Street: string, >>> lat: int32, >>> long: int32? >>> }; >>> >>> create dataset csv_set (csv_type) >>> primary key lat; >>> >>> >>> load dataset csv_set using localfs >>> (("path"="127.0.0.1:///Users/nicholassantini/Downloads/test >>> 2.csv"), >>> ("format"="delimited-text")); >>> >>> >>> SELECT * >>> FROM csv_set csv_type >>> >>> > > -- > Regards, > >
